sql server xml querying?

Question

If I have the XML like the following: (As an XML variable names @nodes)

<Nodes>
    <Item>Item 1</Item>
    <Item>Item 2</Item>
    <Item>Item 3</Item>
</Nodes>

And I also have the index of the item I am looking for (example 2). How can I get the value 'Item 2' using a query?

I have been trying something like this:

  SELECT
@result = @nodes.value('(/Nodes/Item)[2]', 'nvarchar(255)')

But all I can seem to get back is NULL

bobs · Accepted Answer · 2011-03-11 17:40:48Z

1

Your approach works for me.

Here's how I assigned the XML to the variable.

declare @nodes as xml 
set @nodes = '<Nodes>
    <Item>Value 5</Item>
    <Item>Localhost</Item>
    <Item>Unrouteable - 10.x.x.x</Item>
    <Item>Unrouteable - 172.16-31.x.x</Item><Item>Unrouteable - 192.168.x.x</Item>
</Nodes>'

SELECT
@nodes.value('(/Nodes/Item)[2]', 'nvarchar(255)')

answered Mar 11, 2011 at 17:40

bobs

22.3k12 gold badges71 silver badges81 bronze badges

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

1 Comment

Kyle Over a year ago

Ah damn.. I changed a couple things when I was pasting my example.. guess I must have fixed it then. Thanks!

Collectives™ on Stack Overflow

sql server xml querying?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related