Deleting XML nodes - 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 XML nodes

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-30 : 10:18:02

I have an xml file that related to several customers and I want to edit the xml file for each customer. I am using a cursor for other information and within the cursor I am importing the original xml file into a temp table.

DECLARE @FileName varchar(4000) = 'C:\temp\TEST.xml'

DECLARE @SQL NVARCHAR(4000)
DECLARE @PARMS NVARCHAR(1000)
DECLARE @XMLVAR XML

Create Table #Xml
(
XMLText [varchar](max)
)

DECLARE @XML NVARCHAR(4000);
SET @XML = 'INSERT INTO #Xml (XMLText) SELECT * FROM OPENROWSET( BULK ''' + @FileName + ''',SINGLE_BLOB) AS x';
exec sp_executesql @XML ;

SELECT XMLText FROM #Xml
DROP Table #Xml
Each customer has a section of the xml file ( sample below ) . what I would like to do is parm in an account number for instance 22222222 and the other sections be removed

original file
<?xml version="1.0" encoding="ISO-8859-1"?>

<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2013-03-12"></Header>
<AddresseeInformation name="THIS COMPANY"></AddresseeInformation>
<ServiceLicenseInformation userName="MY COMPANY" userNumber="000000"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="TEST1" number="11111111" ></OriginatingAccount>
<ReturnedDebitItem ref="AAAAAAA" transCode="17" returnCode="0067" returnDescription="REFER TO PAYER" valueOf="108.24" currency="GBP">
<ReturnedDebitItem ref="BBBBBBB" transCode="17" returnCode="6067" returnDescription="NO INSTRUCTION" valueOf="191.16" currency="GBP">
<ReturnedDebitItem ref="CCCCCCC" transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" valueOf="160.49" currency="GBP">
<Totals numberOf="3" valueOf="459.89" currency="GBP"></Totals>
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="TEST2" number="22222222" ></OriginatingAccount>
<ReturnedDebitItem ref="AAAAAAA" transCode="17" returnCode="0067" returnDescription="REFER TO PAYER" valueOf="108.24" currency="GBP">
<ReturnedDebitItem ref="BBBBBBB" transCode="17" returnCode="6067" returnDescription="NO INSTRUCTION" valueOf="191.16" currency="GBP">
<ReturnedDebitItem ref="CCCCCCC" transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" valueOf="160.49" currency="GBP">
<Totals numberOf="3" valueOf="459.89" currency="GBP"></Totals>
</OriginatingAccountRecord>
<OriginatingAccountRecord>
<OriginatingAccount name="TEST3" number="33333333" ></OriginatingAccount>
<ReturnedDebitItem ref="AAAAAAA" transCode="17" returnCode="0067" returnDescription="REFER TO PAYER" valueOf="108.24" currency="GBP">
<ReturnedDebitItem ref="BBBBBBB" transCode="17" returnCode="6067" returnDescription="NO INSTRUCTION" valueOf="191.16" currency="GBP">
<ReturnedDebitItem ref="CCCCCCC" transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" valueOf="160.49" currency="GBP">
<Totals numberOf="3" valueOf="459.89" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data>
</BACSDocument>

required result to be left in the temp table

<?xml version="1.0" encoding="ISO-8859-1"?>

<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT1019" adviceNumber="999" currentProcessingDate="2013-03-12"></Header>
<AddresseeInformation name="THIS COMPANY"></AddresseeInformation>
<ServiceLicenseInformation userName="MY COMPANY" userNumber="000000"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="TEST2" number="22222222" ></OriginatingAccount>
<ReturnedDebitItem ref="AAAAAAA" transCode="17" returnCode="0067" returnDescription="REFER TO PAYER" valueOf="108.24" currency="GBP">
<ReturnedDebitItem ref="BBBBBBB" transCode="17" returnCode="6067" returnDescription="NO INSTRUCTION" valueOf="191.16" currency="GBP">
<ReturnedDebitItem ref="CCCCCCC" transCode="17" returnCode="1067" returnDescription="INSTRUCTION CANCELLED" valueOf="160.49" currency="GBP">
<Totals numberOf="3" valueOf="459.89" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data>
</BACSDocument>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-30 : 13:26:31

SQL Server has very limited XML editing capabities. The modify method which is what you would use is rather limited in my opinion. Your best bet here would be to shred the XML into a relation (virtual or real) table and then reconstitute the XML the way you want using xml path. The following is a simple example of how you can do this. I am using a CTE. You don't have to. You can put the data into a temp table or permanent table and then construct the XML.

DECLARE @x XML = '<root><a name="test" amt="10"/><a name="test" amt="12"/></root>';

;WITH cte AS
(
	SELECT
		c.value('@name[1]','varchar(32)') AS name,
		c.value('@amt[1]','float') amt
	FROM
		@x.nodes('//a') T(c)
)
SELECT
	name,
	SUM(amt) AS amounts
FROM
	cte 
GROUP BY 
	name
FOR XML PATH('a'), ROOT('root')

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-30 : 14:26:47

Hi James,

I don't think there is any real need to shred and rebuild the xml as its only being put in storage for internal use.

As the xml files are fairly small I have settled with a cursor and
Set @Command = 'UPDATE #Xml SET XMLText.modify(''delete /BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/*['+ @myID + ']'')'

Thanks as always though for your response

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-30 : 14:37:34

Your XML is not wellformed. Your ReturnedDebitItem elements are missing a closing element.

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

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-31 : 06:27:47

SwePeso, my apologies, I had just cut down a live xml file to show as an example.

Taking what I know with
Set @Command = 'UPDATE #Xml SET XMLText.modify(''delete /BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/*['+ @myID + ']'')'

which will delete the nodes I specify
In my poorly formed xml previously, is it possible to delete all nodes that 'number' does not contain '22222' ?

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-31 : 13:04:21

I have been looking on various sites trying to accomplish deleting a node based on a partial value of an attribute. I have come up with the following, which although doesn't throw any error , it doesn't remove the node either

In the real xml file, there is an attribute @ref with a value of '217211ABCD' As the real XML file contains multiple entries for '217111xxxxx' as well as other customers, I am looking for to delete by supplying a partial value for @ref

SET XMLText.modify( 'delete /BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem[substring(@ref, 0, 6) = ''217211'']')

can any one help with this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-31 : 15:18:45

For wildcard searches, use CONTAINS.

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

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-31 : 15:29:36

Hi Swepeso,

I tried SET XMLText.modify( 'delete /BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem[contains(.,''217211'')]')

but that nodes with ref="217211abc" and node with ref="217211123" are still in the xml
Am I using it correctly. ref will contain values like "101010abc", "235547aaa", "217211abc" etc, what I want is to be able to parm in "217211" and all nodes that have ref="217211 xxx " to be removed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources