Deleting in XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deleting in XML

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-10-04 : 12:30:50

I know how to delete from an xml file using
Set @SQL = 'UPDATE #Xml SET XMLText.modify(''delete /BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem[@ref[not(contains(.,''''' + @mLicence + '''''))]]'')'
exec sp_executesql @SQL

If I have am xml file containing 4 records, is it possible to delete two of those records if they do not appear in a table
ie if in my table 2 records, fields; Sortcode, accountnumber, how can I delete the records from the xml file that the sortcode value and accountnumber values do not match, and end up with an xml file with just the two records in it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-04 : 13:52:50

1) Yes.
2) Why are you using dynamic SQL?

UPDATE	#Xml 
SET	XMLText.modify('delete (/BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem[not(contains(@ref, sql:variable("@mLicence")))])');

Also see
http://weblogs.sqlteam.com/peterl/archive/2008/03/26/Some-XML-search-approaches.aspx
http://weblogs.sqlteam.com/peterl/archive/2008/09/01/Updated-XML-search-test-case-with-variables.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx
http://weblogs.sqlteam.com/peterl/archive/2009/08/04/Manipulate-XML-data-continued.aspx

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources