Separate XML node values into separate rows in SQL

Question

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

Please help! Thanks in advance.

TT. · Accepted Answer · 2016-10-31 19:48:21Z

1

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);

edited Oct 31, 2016 at 19:48

answered Oct 31, 2016 at 19:42

TT.

16.2k6 gold badges53 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TT. Over a year ago

Let me rewrite that for a table... Sorry fast poster ;)

nmess88 Over a year ago

Thanks!! that is exactly what i was looking for.

Collectives™ on Stack Overflow

Separate XML node values into separate rows in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related