Convert XML Nodes To Rows in SQL Server

Question

Consider the below sample:

declare @somexml as xml

set @somexml = '
<Settings>
    <Users>
        <ID>1</ID>
        <ID>2</ID>
        <ID>3</ID>
        <ID>4</ID>
        <ID>5</ID>
    </Users>
</Settings>'

The above XML has some ID values that I need to convert to rows of data that can be used in a temp table to perform joins against.

I can't quite get the syntax correct, I've tried a number of samples that I've come across:

SELECT T.r.value('.','int') as id
FROM @somexml.nodes('/Settings/Users') T(r)

Returns:

|ID    |
|------|
|12345 |

The following:

SELECT T.r.query('.') as id
from @somexml.nodes('/Settings/Users/ID') as T(r)

Returns:

|ID         |
|-----------|
|<ID>1</ID> |
|<ID>2</ID> |
|<ID>3</ID> |
|<ID>4</ID> |
|<ID>5</ID> |

I'm getting close, but I want to remove the XML tags to just leave the ID values like so:

|ID |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |

Here's a fiddle if you want to play/solve that way: SQL Fiddle

Any help is appreciated as always.

GriGrim · Accepted Answer · 2014-03-17 10:56:45Z

7

Replace T.r.query('.') as id with T.r.value('.', 'INT') as id

answered Mar 17, 2014 at 10:56

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

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

Comments

Ortsbo · Accepted Answer · 2018-05-11 23:17:35Z

1

SELECT T.r.value('.','int') as id
FROM @somexml.nodes('/Settings/Users/ID') T(r)

answered May 11, 2018 at 23:17

Ortsbo

1731 silver badge7 bronze badges

Collectives™ on Stack Overflow

Convert XML Nodes To Rows in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related