user7043412 user7043412 - 20 days ago 8
SQL Question

How to get multiple rows from multiple nodes in a XMLData field?

I have this select in SQL:



DECLARE @myOrder xml
SET @myOrder =
'<genxml>
<files />
<hidden />
<textbox />
<checkbox />
<dropdownlist>
<orderstatus>040</orderstatus>
</dropdownlist>
<checkboxlist />
<radiobuttonlist />
<carteditmode />
<clientmode>False</clientmode>
<clientdisplayname />
<totalqty datatype="double">3</totalqty>
<totalweight datatype="double">700</totalweight>
<totalunitcost datatype="double">17.28</totalunitcost>
<subtotalcost datatype="double">26.33</subtotalcost>
<subtotaldealercost datatype="double">21.9</subtotaldealercost>
<subtotal datatype="double">26.33</subtotal>
<appliedsubtotal datatype="double">28.23</appliedsubtotal>
<discountstatus datatype="double" />
<voucherdiscount datatype="double">0</voucherdiscount>
<totaldealerdiscount datatype="double">0</totaldealerdiscount>
<applieddiscount datatype="double">6.06</applieddiscount>
<totaldealerbonus datatype="double">4.43</totaldealerbonus>
<totaldiscount datatype="double">6.06</totaldiscount>
<totalsalediscount datatype="double">1.9</totalsalediscount>
<taxcost datatype="double">2.01</taxcost>
<appliedtax datatype="double">0</appliedtax>
<dealertotal datatype="double">17.74</dealertotal>
<total datatype="double">22.17</total>
<appliedtotal datatype="double">22.17</appliedtotal>
<currentcartstage>cartsummary</currentcartstage>
<shippingcost datatype="double">0</shippingcost>
<shippingdealercost datatype="double">0</shippingdealercost>
<appliedshipping datatype="double">0</appliedshipping>
<shippingproductcode />
<pickuppointref />
<pickuppointaddr />
<lang />
<discountprocessed>True</discountprocessed>
<billaddress>
<genxml>
<files />
<hidden />
<textbox>
<firstname>Hans</firstname>
<lastname>Lastname</lastname>
<telephone>0000000</telephone>
<email datatype="email">info@test.com</email>
<company />
<street>Streetname</street>
<unit />
<city>Cityname</city>
<postalcode>1234AA</postalcode>
</textbox>
<checkbox />
<dropdownlist>
<selectaddress selectedtext="" />
<country selectedtext="Netherlands">NL</country>
<region selectedtext="Overijssel">Country.NL.Region:OV</region>
</dropdownlist>
<checkboxlist />
<radiobuttonlist />
</genxml>
</billaddress>
<extrainfo>
<genxml>
<files />
<hidden>
<shippingproductcode />
<pickuppointref />
<pickuppointaddr />
</hidden>
<textbox>
<extramessage />
<promocode>E2COLGQS</promocode>
</textbox>
<checkbox>
<chknews>True</chknews>
</checkbox>
<dropdownlist />
<checkboxlist />
<radiobuttonlist>
<shippingprovider>afhalen op donderdag</shippingprovider>
<rblshippingoptions />
</radiobuttonlist>
</genxml>
</extrainfo>
<isvalidated>True</isvalidated>
<createddate datatype="date">2016-11-11T19:34:52</createddate>
<ordernumber>161111263</ordernumber>
<audit>
<genxml>
<date>2016-11-11T19:34:48</date>
<status>010</status>
<username>hans</username>
<showtouser>True</showtouser>
</genxml>
<genxml>
<date>2016-11-11T19:34:49</date>
<status>020</status>
<username>hans</username>
<showtouser>True</showtouser>
</genxml>
<genxml>
<date>2016-11-11T19:34:49</date>
<status>010</status>
<username>hans</username>
<showtouser>True</showtouser>
</genxml>
<genxml>
<date>2016-11-11T19:34:52</date>
<status>040</status>
<username />
<showtouser>True</showtouser>
</genxml>
</audit>
<paymentproviderkey>molliepayment</paymentproviderkey>
<posturl>https://www.mollie.com/paymentscreen/ideal/testmode?transaction_id=e7e2ee381d6105c90c74ff5d203eeb1a&amp;bank_trxid=0000000002656871</posturl>
<items>
<genxml>
<productid>125</productid>
<modelid>KeYZQZUC</modelid>
<qty>2</qty>
<productname>Titel van product</productname>
<summary>Samenvatting van product</summary>
<modelref>1KG</modelref>
<modeldesc>! kg verpakking</modeldesc>
<modelextra>Extra omschrijving bij model</modelextra>
<unitcost>10.95</unitcost>
<dealercost>10.95</dealercost>
<taxratecode>2</taxratecode>
<saleprice>0</saleprice>
<basecost>10.95</basecost>
<isdealer>False</isdealer>
<options />
<itemcode>125-KeYZQZUC</itemcode>
<textbox />
<dropdownlist />
<radiobuttonlist />
<checkbox />
<totalweight datatype="double">400</totalweight>
<totalcost datatype="double">21.9</totalcost>
<totaldealercost datatype="double">21.9</totaldealercost>
<totaldealerbonus datatype="double">0</totaldealerbonus>
<discountcodeamt datatype="double">4.16</discountcodeamt>
<discountstatus datatype="double">valid</discountstatus>
<totaldiscount datatype="double">4.16</totaldiscount>
<salediscount datatype="double">0</salediscount>
<totaldealerdiscount datatype="double">0</totaldealerdiscount>
<appliedtotalcost datatype="double">21.9</appliedtotalcost>
<appliedcost datatype="double">10.95</appliedcost>
<taxcost datatype="double">1.24</taxcost>
<promodiscount datatype="double">0</promodiscount>
<productxml>
<genxml>
<files />
<hidden />
<textbox>
<txtproductref>SKU</txtproductref>
</textbox>
<checkbox>
<chkishidden>False</chkishidden>
<chkdisable>False</chkdisable>
</checkbox>
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
<models>
<genxml>
<files />
<hidden>
<modelid>KeYZQZUC</modelid>
</hidden>
<textbox>
<availabledate datatype="date" />
<txtqtyminstock datatype="double">1</txtqtyminstock>
<txtmodelref>1KG</txtmodelref>
<txtunitcost datatype="double">10.95</txtunitcost>
<txtsaleprice datatype="double">0</txtsaleprice>
<txtbarcode>BAR1234</txtbarcode>
<txtqtyremaining datatype="double">500</txtqtyremaining>
<txtqtystockset datatype="double">500</txtqtystockset>
<txtdealercost datatype="double">10.95</txtdealercost>
<txtpurchasecost datatype="double">0</txtpurchasecost>
<weight datatype="double">200</weight>
<depth datatype="double">20</depth>
<width datatype="double">30</width>
<height datatype="double">50</height>
<unit>1</unit>
<delay />
</textbox>
<checkbox>
<chkstockon>True</chkstockon>
<chkishidden>False</chkishidden>
<chkdeleted>False</chkdeleted>
<chkdealeronly>False</chkdealeronly>
</checkbox>
<dropdownlist>
<modelstatus>010</modelstatus>
<taxrate>2</taxrate>
</dropdownlist>
<checkboxlist />
<radiobuttonlist />
</genxml>
</models>
<imgs>
<genxml>
<files />
<hidden>
<imageurl>/images/gwSKry3s.jpg</imageurl>
<imagepath>\images\gwSKry3s.jpg</imagepath>
</hidden>
<textbox />
<checkbox>
<chkhidden />
</checkbox>
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
<genxml>
<files />
<hidden>
<imageurl>/images/8bHyZkZi.png</imageurl>
<imagepath>\images\8bHyZkZi.png</imagepath>
</hidden>
<textbox />
<checkbox>
<chkhidden />
</checkbox>
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</imgs>
<options />
<docs>
<genxml>
<files />
<hidden>
<fileext>.png</fileext>
<filepath>d78.png</filepath>
<filename>d78.png</filename>
<filerelpath>/d78.png</filerelpath>
</hidden>
<textbox>
<txtfilename>Opzetje-Logo.png</txtfilename>
</textbox>
<checkbox>
<chkishidden>False</chkishidden>
<chkpurchase>False</chkpurchase>
</checkbox>
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</docs>
<importref>nxw73yabsx</importref>
<lang>
<genxml>
<files />
<hidden />
<textbox>
<txtproductname>Titel van product</txtproductname>
<txtsummary>Samenvatting van product</txtsummary>
<txtseoname>SEO Naam</txtseoname>
<txtseopagetitle>SEO Titel</txtseopagetitle>
<txttagwords>E-nummer vrij</txttagwords>
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
<edt>
<description>&lt;p&gt;Dit is een faketekst. Alles wat hier staat is slechts om een indruk te geven van het grafische effect van tekst op deze plek. Wat u hier leest is een voorbeeldtekst. Deze wordt later vervangen door de uiteindelijke tekst, die nu nog niet bekend is. De faketekst is dus een tekst die eigenlijk nergens over gaat. Het grappige is, dat mensen deze toch vaak lezen. Zelfs als men weet dat het om een faketekst gaat, lezen ze toch door.&lt;/p&gt;
</description>
</edt>
<models>
<genxml>
<files />
<hidden />
<textbox>
<txtmodelname>! kg verpakking</txtmodelname>
<txtextra>Extra omschrijving bij model</txtextra>
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</models>
<options />
<imgs>
<genxml>
<files />
<hidden />
<textbox>
<txtimagedesc />
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
<genxml>
<files />
<hidden />
<textbox>
<txtimagedesc />
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</imgs>
<docs>
<genxml>
<files />
<hidden />
<textbox>
<txtdocdesc />
<txttitle />
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</docs>
</genxml>
</lang>
</genxml>
</productxml>
</genxml>
<genxml>
<productid>206</productid>
<modelid>7vem1mvV</modelid>
<qty>1</qty>
<productname>Productname </productname>
<summary>Productsummary
</summary>
<modelref />
<modeldesc />
<modelextra />
<unitcost>6.33</unitcost>
<dealercost>0</dealercost>
<taxratecode>1</taxratecode>
<saleprice>4.43</saleprice>
<basecost>6.33</basecost>
<isdealer>False</isdealer>
<options />
<itemcode>206-7vem1mvV</itemcode>
<textbox />
<dropdownlist />
<radiobuttonlist />
<checkbox />
<promodiscount datatype="double">0</promodiscount>
<totalweight datatype="double">300</totalweight>
<totalcost datatype="double">4.43</totalcost>
<totaldealercost datatype="double">0</totaldealercost>
<totaldealerbonus datatype="double">4.43</totaldealerbonus>
<totaldiscount datatype="double">1.9</totaldiscount>
<salediscount datatype="double">1.9</salediscount>
<totaldealerdiscount datatype="double">0</totaldealerdiscount>
<appliedtotalcost datatype="double">4.43</appliedtotalcost>
<appliedcost datatype="double">4.43</appliedcost>
<taxcost datatype="double">0.77</taxcost>
<productxml>
<genxml>
<files />
<hidden />
<textbox>
<txtproductref>749022</txtproductref>
</textbox>
<checkbox>
<chkishidden>False</chkishidden>
<chkdisable>False</chkdisable>
</checkbox>
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
<models>
<genxml>
<files />
<hidden>
<modelid>7vem1mvV</modelid>
</hidden>
<textbox>
<availabledate datatype="date" />
<txtqtyminstock datatype="double">0</txtqtyminstock>
<txtmodelref />
<txtunitcost datatype="double">6.33</txtunitcost>
<txtsaleprice datatype="double">4.43</txtsaleprice>
<txtbarcode />
<txtqtyremaining datatype="double">0</txtqtyremaining>
<txtqtystockset datatype="double">0</txtqtystockset>
<txtdealercost datatype="double">0</txtdealercost>
<txtpurchasecost datatype="double">0</txtpurchasecost>
<weight datatype="double">300</weight>
<depth datatype="double">0</depth>
<width datatype="double">0</width>
<height datatype="double">0</height>
<unit />
<delay />
</textbox>
<checkbox>
<chkstockon>False</chkstockon>
<chkishidden>False</chkishidden>
<chkdeleted>False</chkdeleted>
<chkdealeronly>False</chkdealeronly>
</checkbox>
<dropdownlist>
<modelstatus>010</modelstatus>
<taxrate>1</taxrate>
</dropdownlist>
<checkboxlist />
<radiobuttonlist />
</genxml>
</models>
<imgs>
<genxml>
<files />
<hidden>
<imageurl>/images/32dD5o65.jpg</imageurl>
<imagepath>\images\32dD5o65.jpg</imagepath>
</hidden>
<textbox />
<checkbox>
<chkhidden />
</checkbox>
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</imgs>
<options />
<docs />
<importref>pys6ox9d8s</importref>
<lang>
<genxml>
<files />
<hidden />
<textbox>
<txtproductname>Producttitel</txtproductname>
<txtsummary>Productsummary
</txtsummary>
<txtseoname>SEO NAME PRoduct </txtseoname>
<txtseopagetitle />
<txttagwords />
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
<edt>
<description>&lt;p&gt;Lot of text
</description>
</edt>
<models>
<genxml>
<files />
<hidden />
<textbox>
<txtmodelname />
<txtextra />
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</models>
<options />
<imgs>
<genxml>
<files />
<hidden />
<textbox>
<txtimagedesc>img description</txtimagedesc>
</textbox>
<checkbox />
<dropdownlist />
<checkboxlist />
<radiobuttonlist />
</genxml>
</imgs>
<docs />
</genxml>
</lang>
</genxml>
</productxml>
</genxml>
</items>
</genxml>'

SELECT T.C.value('.', 'varchar(100)') as productid
FROM @myOrder.nodes('(/genxml/items/genxml/productid)') as T(C)





Which gives a nice two rows with the two products in this order.
But how can i get an few extra columns, with f.i. the productname which is in the node /genxml/items/genxml/productname

Answer

One option is to stop at genxml instead of navigating 1 more level down to product and then select your details.

Update: Added one more level of navigation. You can keep extending this for each level depending where you start. Since we went down to /genxml/items/genxml/ in T(C) we then use this level and go down again in T1(C2). I went down to this extent (sample) to keep my select part small/readable.

  SELECT 
     T.C.query('productid').value('.', 'varchar(100)') AS productid 
    ,T.C.query('productname').value('.', 'varchar(100)') productname
    ,T.C.query('totalweight').value('.', 'float') totalweight
    ,T1.C2.query('txtbarcode').value('.', 'varchar(100)') txtbarcode
    FROM @myOrder.nodes('(/genxml/items/genxml/.)') as T(C)    
    CROSS APPLY T.c.nodes('(productxml/genxml/models/genxml/textbox/.)') as T1(C2)
    --few levels down navigatig all the way to textbox       

The query is only for navigating product details with sample given. Hope it helps.

Comments