nmess88 nmess88 - 1 month ago 5
SQL Question

Separate XML node values into separate rows in SQL

I have a SQL table with a XML column. The value of the column looks like this:

<StudentGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<StudentIds>
<int>3000</int>
<int>3001</int>
<int>3002</int>
<int>8</int>
<int>9</int>
</StudentIds>
</StudentGroup>


I want to get the each StudentIDs in a separate row instead of it in one row. Here is what I have done:

select
xmlColumn.value('(/StudentGroup/StudentIds)[1]','varchar(max)') as IDs
from myTable


This select statement returns the IDs in one row. Like this:

30003001300289


What is want is

3000
3001
3002
8
9


Please help! Thanks in advance.

TT. TT.
Answer

When you have the XML in a variable:

DECLARE @x XML = '
<StudentGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <StudentIds>
    <int>3000</int>
    <int>3001</int>
    <int>3002</int>
    <int>8</int>
    <int>9</int>
  </StudentIds>
</StudentGroup>';

SELECT
    n.v.value('.','INT') AS ID
FROM
    @x.nodes('/StudentGroup/StudentIds/int') AS n(v);

When you have the XML in a table:

DECLARE @x XML = '
<StudentGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <StudentIds>
    <int>3000</int>
    <int>3001</int>
    <int>3002</int>
    <int>8</int>
    <int>9</int>
  </StudentIds>
</StudentGroup>';

DECLARE @t TABLE(
    x XML
);
INSERT INTO @t(x)VALUES(@x);

SELECT
    n.v.value('.','INT') AS ID
FROM
    @t
    CROSS APPLY x.nodes('/StudentGroup/StudentIds/int') AS n(v);