Extract data from an xml fragment

Question

In SQL Server 2014, I have the following xml fragment :

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

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

Answer 1

With your XML in the variable @X:

select @X.value('(salesorder/ordnum/text())[1]', 'int') as ordnum,
       T.X.value('(itemtype/text())[1]', 'varchar(15)') as itemtype,
       T.X.value('(specifications/specification[name/text() = "Qty"]/value/text())[1]', 'int') as qty,
       T.X.value('(specifications/specification[name/text() = "Price"]/value/text())[1]', 'money') as price
from @X.nodes('/salesorder/details/detail') as T(X);

The most complicated part of this query is probably the XQuery expression used to get the values for Qty and Price.

[name/text() = "Qty"] is a predicate that looks at the value for the node name and returns true if it is Qty.

The rest is basic XML query stuff using nodes() to shred the XML on /salesorder/details/detail to get one row per detail element and value() to extract the values you want from your XML.

Answer 2

Below t-sql code will surely help you.

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

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.