Extract data from an xml fragment In SQL Server 2014, I have the following xml fragment :

&lt;salesorder&gt;
   &lt;ordnum&gt;123&lt;/ordnum&gt;
   &lt;orddate&gt;2017-04-17&lt;/orddate&gt;
   &lt;details&gt;
      &lt;detail&gt;
         &lt;itemtype&gt;Stationery&lt;/itemtype&gt;
         &lt;itemdesc&gt;Pencil&lt;/itemdesc&gt;
         &lt;specifications&gt;
            &lt;specification&gt;
               &lt;name&gt;Qty&lt;/name&gt;
               &lt;value&gt;5&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Description&lt;/name&gt;
               &lt;value&gt;Pencil H1&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Price&lt;/name&gt;
               &lt;value&gt;1.50&lt;/value&gt;
            &lt;/specification&gt;
         &lt;/specifications&gt;
      &lt;/detail&gt;
      &lt;detail&gt;
         &lt;itemtype&gt;Stationery&lt;/itemtype&gt;
         &lt;itemdesc&gt;Pencil&lt;/itemdesc&gt;
         &lt;specifications&gt;
            &lt;specification&gt;
               &lt;name&gt;Qty&lt;/name&gt;
               &lt;value&gt;10&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Description&lt;/name&gt;
               &lt;value&gt;Pencil H2&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Colour&lt;/name&gt;
               &lt;value&gt;Red&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Price&lt;/name&gt;
               &lt;value&gt;2.75&lt;/value&gt;
            &lt;/specification&gt;
         &lt;/specifications&gt;
      &lt;/detail&gt;
   &lt;/details&gt;
&lt;/salesorder&gt;


I would like to extract the following data :

ordnum   itemtype     qty   price
123      Stationery   5     1.50
123      Stationery   10    2.75


Is this possible to accomplish in T-SQL?

Thanks in advance,
MP
Below t-sql code will surely help you.

DECLARE @vXML XML;
SET @vXML = '&lt;salesorder&gt;
   &lt;ordnum&gt;123&lt;/ordnum&gt;
   &lt;orddate&gt;2017-04-17&lt;/orddate&gt;
   &lt;details&gt;
      &lt;detail&gt;
         &lt;itemtype&gt;Stationery&lt;/itemtype&gt;
         &lt;itemdesc&gt;Pencil&lt;/itemdesc&gt;
         &lt;specifications&gt;
            &lt;specification&gt;
               &lt;name&gt;Qty&lt;/name&gt;
               &lt;value&gt;5&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Description&lt;/name&gt;
               &lt;value&gt;Pencil H1&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Price&lt;/name&gt;
               &lt;value&gt;1.50&lt;/value&gt;
            &lt;/specification&gt;
         &lt;/specifications&gt;
      &lt;/detail&gt;
      &lt;detail&gt;
         &lt;itemtype&gt;Stationery&lt;/itemtype&gt;
         &lt;itemdesc&gt;Pencil&lt;/itemdesc&gt;
         &lt;specifications&gt;
            &lt;specification&gt;
               &lt;name&gt;Qty&lt;/name&gt;
               &lt;value&gt;10&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Description&lt;/name&gt;
               &lt;value&gt;Pencil H2&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Colour&lt;/name&gt;
               &lt;value&gt;Red&lt;/value&gt;
            &lt;/specification&gt;
            &lt;specification&gt;
               &lt;name&gt;Price&lt;/name&gt;
               &lt;value&gt;2.75&lt;/value&gt;
            &lt;/specification&gt;
         &lt;/specifications&gt;
      &lt;/detail&gt;
   &lt;/details&gt;
&lt;/salesorder&gt;';


    

SELECT  
       salesorder.Col.value('ordnum[1]', 'int') 
       , detail.Col.value('itemtype[1]', 'varchar(255)')
       , specification_qty.Qty AS Qty
       , specification_price.Price AS Price
FROM @vXML.nodes('//salesorder') AS salesorder(Col)
    CROSS APPLY salesorder.Col.nodes('details') AS details(Col)
    CROSS APPLY details.Col.nodes('detail') AS detail(Col)
    CROSS APPLY detail.Col.nodes('specifications') AS specifications(Col)
    CROSS APPLY (SELECT specification.Col.value('value[1]', 'int') AS Qty FROM specifications.Col.nodes('specification') AS specification(Col) WHERE specification.Col.value('name[1]','VARCHAR(255)') = 'Qty') AS specification_qty
    CROSS APPLY (SELECT specification.Col.value('value[1]', 'decimal(3,2)') AS Price FROM specifications.Col.nodes('specification') AS specification(Col) WHERE specification.Col.value('name[1]','VARCHAR(255)') = 'Price') AS specification_price


Little description about the solution:

As the provided XML is a multilevel XML, we will need to process the data in chunk. Hence we need to segregate the multiple levels of xmls in multiple tables and then cross apply them to get the required result. If it was a single level XML data then a single SELECT * FROM Tbl method could have worked.

Reference Links:

Single level XML solution provided by Sanchitos on Stack Overflow 

Multilevel XML solution provided by George T on Stack Overflow

Query XML in SQL Server for Different Hierarchy Levels using Cross Apply

Hope this will help.