Parse simple XML in SQL Server

Question

How to parse the following xml as recordset?

<root>
    <240>0</240>
    <241>1</241>
    <242>2</242>
    <243>3</243>
    <249>4</249>
</root>

<root 240="0" 241="1" 242="2" 243="3" 249="4"/>

When I try

declare @ids xml = N'<root><240>0</240><241>1</241></root>'

SELECT T.Item.value('240[1]', 'int') 
from @ids.nodes('/root') AS T(Item)

I get an error

ML parsing: line 1, character 8, illegal qualified name character: declare @ids xml = N'<240>0' SELECT T.Item.value('a[1]', 'int') from @ids.nodes('/root') AS T(Item)

But generally I need the following output:

|240|0|
|241|1|
...

When xml elements are named as usual everything is Ok (<row key=240 value="0"/>).

Anton · Accepted Answer · 2013-07-05 09:08:06Z

1

XML disallows to use numbers as first character of an element name. Use format from your example:

<row key=240 value="0"/>

answered Jul 5, 2013 at 9:08

Anton

10k12 gold badges43 silver badges74 bronze badges

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

Collectives™ on Stack Overflow

Parse simple XML in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related