Transforming XML Data into Relational Datasets in SQL Server

To transform the XML data into a relational/rectangular dataset, you can use the following SQL statement:

DECLARE @xml XML =
'&lt;dataset xmlns="http://developer.cognos.com/schemas/xmldata/1/"
         xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"&gt;
    &lt;metadata&gt;
        &lt;item name="Task" type="xs:string" length="-1"/&gt;
        &lt;item name="Task Number" type="xs:string" length="-1"/&gt;
        &lt;item name="Group" type="xs:string" length="-1"/&gt;
        &lt;item name="Work Order" type="xs:string" length="-1"/&gt;
    &lt;/metadata&gt;
    &lt;data&gt;
        &lt;row&gt;
            &lt;value&gt;3361B11&lt;/value&gt;
            &lt;value&gt;1&lt;/value&gt;
            &lt;value&gt;01&lt;/value&gt;
            &lt;value&gt;MS7579&lt;/value&gt;
        &lt;/row&gt;
        &lt;row&gt;
            &lt;value&gt;3361B11&lt;/value&gt;
            &lt;value&gt;2&lt;/value&gt;
            &lt;value&gt;50&lt;/value&gt;
            &lt;value&gt;MS7579&lt;/value&gt;
        &lt;/row&gt;
        &lt;row&gt;
            &lt;value&gt;3361B11&lt;/value&gt;
            &lt;value&gt;3&lt;/value&gt;
            &lt;value&gt;02&lt;/value&gt;
            &lt;value&gt;JA0520&lt;/value&gt;
        &lt;/row&gt;
    &lt;/data&gt;
&lt;/dataset&gt;';

WITH XMLNAMESPACES(DEFAULT 'http://developer.cognos.com/schemas/xmldata/1/')
SELECT 
  c.value('(value[1]/text())[1]', 'VARCHAR(20)') AS Task
  , c.value('(value[2]/text())[1]', 'VARCHAR(20)') AS [Task Number]
  , c.value('(value[3]/text())[1]', 'VARCHAR(20)') AS [Group]
  , c.value('(value[4]/text())[1]', 'VARCHAR(20)') AS [Work Order]
FROM @xml.nodes('/dataset/data/row') AS t(c);

This will transform the XML data into a relational/rectangular dataset, where each row represents a single value from the XML, and each column represents a different value. The output of this statement is:

+--------+------------+-------+-------------+
| Task   | [Task Number]  | Group | Work Order |
+========+============+=======+=============+
| 3361B11 |            1 |     01 | MS7579    |
+--------+------------+-------+-------------+
| 3361B11 |            2 |     50 | MS7579    |
+--------+------------+-------+-------------+
| 3361B11 |            3 |     02 | JA0520    |
+--------+------------+-------+-------------+

Please note that the value function in SQL Server returns a single value from a node, so this will not work if you have multiple row elements with different values. You would need to use a more complex query or aggregate function (like GROUP BY, SUM, AVG) to handle such cases.

Also note that the XML data is expected to be in a specific format: it has a single root element (dataset), which contains another element (metadata), and this element again contains multiple child elements (item). The data within the row elements are specified using value elements with sequential order from 1 to 4.

Last modified on 2025-01-21