parsing XML custom attributes and values - HELP!!

• Stuart B.

  Valued Member

  Points: 59

  More actions

  March 15, 2017 at 10:18 pm

  #335736

  Hello,

  Am looking for some advice on how to accomplish the following.

  Consider this fragment of XML

  <product-lineitems>
    <product-lineitem>
     <net-price>44.54</net-price>
     <tax>4.46</tax>
     <gross-price>49.00</gross-price>
     <base-price>49.00</base-price>
     <lineitem-text>pendant</lineitem-text>
     <tax-basis>49.00</tax-basis>
     <position>1</position>
     <product-id>LBPE0001-001-12</product-id>
     <product-name>pendant</product-name>
     <quantity unit="">1.0</quantity>
     <tax-rate>0.1</tax-rate>
     <shipment-id>00134005</shipment-id>
     <gift>false</gift>
     <custom-attributes>
      <custom-attribute attribute-id="ean">4051245264135</custom-attribute>
      <custom-attribute attribute-id="egIsInspired">false</custom-attribute>
      <custom-attribute attribute-id="egTextBMDisplay">TAU</custom-attribute>
      <custom-attribute attribute-id="eg_combination">T</custom-attribute>
      <custom-attribute attribute-id="eg_erp_value">Round_tag;TAU;ARIAL UNI 1L;3;</custom-attribute>
      <custom-attribute attribute-id="eg_fontFamily">FONT_ARIALU</custom-attribute>
      <custom-attribute attribute-id="eg_fontSize">30.0</custom-attribute>
      <custom-attribute attribute-id="eg_text1">TAU</custom-attribute>
      <custom-attribute attribute-id="engraved">true</custom-attribute>
     </custom-attributes>
    </product-lineitem>
    </product-lineitems>

  What i'm looking to do is generate a sql query that will, for each unique <product-ID> tag, return a query result like so

  product-id,attribute,value
  LBPE0001-001-12,ean,4051245264135
  LBPE0001-001-12,egIsInspired,false
  LBPE0001-001-12,egTextBMDisplay,TAU
  LBPE0001-001-12,eg_combination,T
  LBPE0001-001-12,eg_erp_value,Round_tag;TAU;ARIAL UNI 1L;3;
  LBPE0001-001-12,eg_fontFamily ,FONT_ARIALU
  LBPE0001-001-12,eg_fontSize ,30.0
  LBPE0001-001-12,eg_text1,TAU
  LBPE0001-001-12,engraved,true

  Any suggestions?

  Regards,

  StuartB.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  March 16, 2017 at 9:35 pm

  #1933688

  Try this:

  
declare @x xml;
set @x='<product-lineitems>
<product-lineitem id="1">
<net-price>44.54</net-price>
<tax>4.46</tax>
<gross-price>49.00</gross-price>
<base-price>49.00</base-price>
<lineitem-text>pendant</lineitem-text>
<tax-basis>49.00</tax-basis>
<position>1</position>
<productid>LBPE0001-001-12</productid>
<product-name>pendant</product-name>
<quantity unit="">1.0</quantity>
<tax-rate>0.1</tax-rate>
<shipment-id>00134005</shipment-id>
<gift>false</gift>
<custom-attributes>
<custom-attribute attribute-id="ean">4051245264135</custom-attribute>
<custom-attribute attribute-id="egIsInspired">false</custom-attribute>
<custom-attribute attribute-id="egTextBMDisplay">TAU</custom-attribute>
<custom-attribute attribute-id="eg_combination">T</custom-attribute>
<custom-attribute attribute-id="eg_erp_value">Round_tag;TAU;ARIAL UNI 1L;3;</custom-attribute>
<custom-attribute attribute-id="eg_fontFamily">FONT_ARIALU</custom-attribute>
<custom-attribute attribute-id="eg_fontSize">30.0</custom-attribute>
<custom-attribute attribute-id="eg_text1">TAU</custom-attribute>
<custom-attribute attribute-id="engraved">true</custom-attribute>
</custom-attributes>
</product-lineitem>
</product-lineitems>'

  select p.value('(productid)[1]','varchar(50)') prodid,
              c.value('@attribute-id','varchar(50)') attrid,
               c.value('.','varchar(50)') attr
  from @x.nodes('/product-lineitems/product-lineitem') x(p)
   cross apply p.nodes('custom-attributes/custom-attribute') p(c)
  

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• March 18, 2017 at 10:58 pm

  This was removed by the editor as SPAM