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

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

Using django-mptt to get only the categories that have items -