Delete XML Child Node Element in SQL Server 2008 -
i have xml, want insert temp table including values inside <salesorpurchase>
node in single row. possible?
or can tell me how remove <salesorpurchase>
without removing inner text`?
<itemserviceret> <listid>80000012-1302270176</listid> <editsequence>1302270195</editsequence> <name>2nd floor shop</name> <fullname>2nd floor shop</fullname> <isactive>true</isactive> <salesorpurchase> <price>0.00</price> <accountref> <listid>800000b3-1302260225</listid> <fullname>rent income:rent income 2nd fl:2nd floor shops</fullname> </accountref> </salesorpurchase> </itemserviceret> <itemserviceret> <listid>80000002-1277187768</listid> <editsequence>1463398389</editsequence> <name>vat 16%</name> <fullname>vat 16%</fullname> <isactive>true</isactive> <salesorpurchase> <pricepercent>16.00</pricepercent> <accountref> <listid>6b0000-1224749077</listid> <fullname>vat account</fullname> </accountref> </salesorpurchase> </itemserviceret>
1) try following query insert data every itemserviceret
element 1 row (in case, because there 2 itemserviceret
elements 2 rows inserted):
declare @xmldata xml = n' <itemserviceret> <listid>80000012-1302270176</listid> <editsequence>1302270195</editsequence> <name>2nd floor shop</name> <fullname>2nd floor shop</fullname> <isactive>true</isactive> <salesorpurchase> <price>0.00</price> ... </itemserviceret>' select listid = y.xmlcol.value('(listid)[1]', 'varchar(19)'), editsequence = y.xmlcol.value('(editsequence)[1]', 'int'), -- ... price = y.xmlcol.value('(salesorpurchase/price)[1]', 'numeric(9,2)'), pricepercent = y.xmlcol.value('(salesorpurchase/pricepercent)[1]', 'numeric(9,2)'), listid2 = y.xmlcol.value('(salesorpurchase/accountref/listid)[1]', 'varchar(19)') #items (values (@xmldata)) x(xmlcol)/*or @xmldata.nodes*/ cross apply x.xmlcol.nodes(n'itemserviceret') y(xmlcol) /* results: (2 row(s) affected) */
this solution using 2 xml methods: nodes (shred xml many rows) , value (it extract 1 value current node/element).
2) second solution "deleting" salesorderheader
node thus:
select x.xmlcol.query(' $i in (itemserviceret) return <itemserviceret> <listid>{$i/listid/text()}</listid> <isactive>{$i/isactive/text()}</isactive> <price>{$i/salesorpurchase/price/text()}</price> <pricepercent>{$i/salesorpurchase/pricepercent/text()}</pricepercent> <accountref> <listid>{$i/salesorpurchase/accountref/listid/text()}</listid> <fullname>{$i/salesorpurchase/accountref/fullname/text()}</fullname> </accountref> </itemserviceret> ') newxmlcol (select (@xmldata)) x(xmlcol)
Comments
Post a Comment