Shredding XML column data into rows in SQL

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.

Solution

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 |
+--------------+---------+----+-------+