Parsing the XML Data in columns

Question

Need a help to parse the below XML in column, Language and value basis. I was able to parse the values in row basis. Can anybody please help. The query need is in SQL Server. I have tried writing using OPENXML. But it is not giving me the desired output. Any help will be much appreciated.

<Root>
  <dataModel>
    <repo name="SKU" profile="SKU"/>
  </dataModel>
  <SKU repo="SKU">   
    <SKU_SKU_Number>DR90A3000/U</SKU_SKU_Number> 
    <dynamicAttr>
       <attrName>Power Supply Frequency</attrName>
          <attrValue mLang="1">
            <value lang="en">60 Hz</value>
            <value lang="da-DK"/>
          </attrValue>
          <desc code="">60 Hz</desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Ambient Operating Temperature (Max)</attrName>
          <attrValue mLang="1">
            <value lang="en">90°F </value>
            <value lang="da-DK"/>
          </attrValue>
          <attrValue mLang="1">
            <value lang="en"> 32°C</value>
            <value lang="da-DK"/>
          </attrValue>
          <desc code="">90°F | 32°C</desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Compatible Humidifier Pad</attrName>
          <attrValue mLang="1">
            <value lang="en"/>
            <value lang="da-DK"/>
          </attrValue>
          <desc code=""> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Building Size (Max)</attrName>
          <attrValue mLang="1">
            <value lang="en">Up to 20,000 cubic feet</value>
            <value lang="da-DK"/>
          </attrValue>
          <desc code="">Up to 20,000 cubic feet</desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Recommended Service Life</attrName>
          <attrValue mLang="1">
            <value lang="en">10 Years</value>
            <value lang="da-DK"> </value>
          </attrValue>
          <desc code="">10 Years</desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Current Draw</attrName>
          <attrValue mLang="1">
            <value lang="en">0.625 A</value>
            <value lang="da-DK"> </value>
          </attrValue>
          <desc code="">0.625 A</desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Operating Voltage</attrName>
          <attrValue mLang="1">
            <value lang="en">24 VAC</value>
            <value lang="da-DK">25 VAC</value>
            <value lang="da-OTH">26 VAC</value>
          </attrValue>
          <desc code="">24 VAC</desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Static Pressure (Max)</attrName>
          <attrValue mLang="1">
            <value lang="en">0.3 in wc</value>
            <value lang="da-DK"> </value>
          </attrValue>
          <desc code="">0.3 in wc</desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Duct Opening Width</attrName>
          <attrValue> </attrValue>
          <desc code=""> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Duct Opening Diameter</attrName>
          <attrValue> </attrValue>
          <desc code=""> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Housing Material</attrName>
          <attrValue> </attrValue>
          <desc lang="en"> </desc>
          <desc lang="da-DK"> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Duct Opening Height</attrName>
          <attrValue> </attrValue>
          <desc code=""> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Building Square Footage (Max)</attrName>
          <attrValue mLang="1">
            <value lang="en"> </value>
            <value lang="da-DK"> </value>
          </attrValue>
          <desc code=""> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Power Consumption</attrName>
          <attrValue mLang="1">
            <value lang="en"/>
            <value lang="da-DK"/>
          </attrValue>
          <desc code=""> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Mounting Type</attrName>
          <attrValue> </attrValue>
          <desc lang="en"> </desc>
          <desc lang="da-DK"> </desc>
        </dynamicAttr>
        <dynamicAttr>
          <attrName>Remote Mounting Distance (Max)</attrName>
          <attrValue> </attrValue>
          <desc code=""> </desc>
        </dynamicAttr>
  </SKU>
</Root>

The desired output should be

Answer 1

Assuming you have your XML data in a @XmlData SQL variable - you can use this XQuery to get the data you're looking for:

SELECT
    SkuNumber = XC.value('(SKU_SKU_Number)[1]', 'varchar(50)'),
    AttrName = XC2.value('(attrName)[1]', 'varchar(50)'),
    Lang = xc3.value('@lang', 'varchar(10)'),
    Value = xc3.value('(.)[1]', 'varchar(50)')
FROM
    @XmlData.nodes('/Root/SKU') AS XT(XC)
CROSS APPLY
    XC.nodes('dynamicAttr') AS XT2(XC2)
CROSS APPLY
    XC2.nodes('attrValue/value') AS XT3(XC3)

Output:

The built-in, native XQuery support is strongly preferred over the old, legacy (and partly buggy) OPENXML approach.

Basically, you need two nested "loops" - the first .nodes() call gets you all the <SKU> elements under <Root> (just the one) to grab the data from that node - then you need to "loop" over all the <dynamicAttr> XML elements inside the <SKU> to get some info, and you need to again loop over the contained <attrValue>/<value> subnodes to get the last details.