Shredding XML column data into rows in SQL

Question

I have an xml value

<ITEMS>
<ITEM><ID>1</ID><NAME>John</NAME></ITEM>
<ITEM><ID>5</ID><NAME>James</NAME></ITEM>
</ITEMS>

I am able to shred the above xml into tables of ID and Name column using the below query

Declare @X xml
select x.r.value('(ID)[1]','int') as [ID],
       x.r.value('(DATA)[1]','VARCHAR(100)') AS [DATA]
FROM @X.nodes ('/ITEMS/ITEM') AS x(r)

But how will i able to do this when the above xml is present in a row.

S.No   COMPANY        DATA
 1      ABC      </ITEMS><ITEM><ID>1</ID><NAME>John</Name>....

I need to populate like below

S.No   COMPANY    ID     NAME
 1      ABC        1    John
 2      ABC        5    James

Note : The Data column in the table is of varchar data type and not xml data type.

Yitzhak Khabinsky · Accepted Answer · 2020-02-18T06:08:56.077

First, you can use a CTE to convert it to XML data type. Second way is via a derived table.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (SequentialNo INT PRIMARY KEY, Company VARCHAR(20), [DATA] VARCHAR(MAX));
INSERT INTO @tbl (SequentialNo, Company, [DATA])
VALUES
(1, 'ABC', '<ITEMS>
    <ITEM>
        <ID>1</ID>
        <NAME>John</NAME>
    </ITEM>
    <ITEM>
        <ID>5</ID>
        <NAME>James</NAME>
    </ITEM>
</ITEMS>');
-- DDL and sample data population, end

-- Method #1
-- CTE
;WITH rs AS
(
   SELECT *, TRY_CAST([DATA] AS XML) AS [xmldata]
   FROM @tbl
)
SELECT SequentialNo
    , Company
    , col.value('(ID/text())[1]','INT') AS ID
    , col.value('(NAME/text())[1]','VARCHAR(40)') AS [Name]
FROM rs AS tbl
    CROSS APPLY tbl.[xmldata].nodes('/ITEMS/ITEM') AS tab(col);

-- Method #2
-- Derived table
SELECT SequentialNo
    , Company
    , col.value('(ID/text())[1]','INT') AS ID
    , col.value('(NAME/text())[1]','VARCHAR(40)') AS [Name]
FROM (SELECT *, TRY_CAST([DATA] AS XML) AS [xmldata]
   FROM @tbl) AS tbl
    CROSS APPLY tbl.[xmldata].nodes('/ITEMS/ITEM') AS tab(col);

Output

+--------------+---------+----+-------+
| SequentialNo | Company | ID | Name  |
+--------------+---------+----+-------+
|            1 | ABC     |  1 | John  |
|            1 | ABC     |  5 | James |
+--------------+---------+----+-------+

Thanks it was helping. But is there any possibile solution to do without CTE for casting varchar to xml ? — , Feb 18 '20 at 04:43
This particular query will be used by tableau. The tableau while reading the data doesnot support CTE. So is there any way to use without CTE like (sub queries or something like that) — , Feb 18 '20 at 05:33
Good to hear that the proposed solution is working for you. Please mark it as answered. You simply need to mark an answer as correct (the green check image). Click the green outlined checkmark to the left of the answer that solved your problem. This marks the answer as "accepted" — Yitzhak Khabinsky, Feb 18 '20 at 06:05

Shredding XML column data into rows in SQL

1 Answers1