SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deleting XML nodes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

165 Posts

Posted - 08/30/2013 :  10:18:02  Show Profile  Reply with Quote
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"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<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"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<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
Flowing Fount of Yak Knowledge

3546 Posts

Posted - 08/30/2013 :  13:26:31  Show Profile  Reply with Quote
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')
Go to Top of Page

Pete_N
Posting Yak Master

165 Posts

Posted - 08/30/2013 :  14:26:47  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 08/30/2013 :  14:37:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Your XML is not wellformed. Your ReturnedDebitItem elements are missing a closing element.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Pete_N
Posting Yak Master

165 Posts

Posted - 08/31/2013 :  06:27:47  Show Profile  Reply with Quote
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' ?


Edited by - Pete_N on 08/31/2013 06:29:26
Go to Top of Page

Pete_N
Posting Yak Master

165 Posts

Posted - 08/31/2013 :  13:04:21  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 08/31/2013 :  15:18:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
For wildcard searches, use CONTAINS.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Pete_N
Posting Yak Master

165 Posts

Posted - 08/31/2013 :  15:29:36  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000