asked on

XQuery to return subnodes

Consider the following xml documents in an xml column:

row 1:
<doc>
<children>
<value>1</value>
<value>2</value>
</children>
</doc>

row 2:
<doc>
<children>
<value>3</value>
<value>4</value>
<value>5</value>
</children>
</doc>

I'd like an xquery statement (xml.value()?) to return a rowset of:
1
2
3
4
5

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

here we go:

set ansi_padding on
 
declare @t  table ( data xml )
insert into @t ( data ) values ('
<doc>
  <children>
    <value>1</value>
    <value>2</value>
  </children>
</doc>' )
 
insert into @t ( data ) values ('<doc>
  <children>
    <value>3</value>
    <value>4</value>
    <value>5</value>
  </children>
</doc>' )
 
select t.data , x.v.value('.', 'int')
  from @t t
  cross apply t.data.nodes('/doc/children/value') x (v)

Open in new window

Guy Hengel [angelIII / a3]

too slow :/

Anthony Perkins

And we can only hope the user is using at least SQL Server 2005. :)

Anthony Perkins

Oops, never mind, I need to start reading the question through the end. :)

b_levitt

ASKER

I've said it before and I'll say it again - the SQL Server experts alone are worth a paid membership to EE! I'll give it a shot in the morning and award points.

chapmandew

b_levitt

ASKER

Works great chap. Sorry you lost the race angel.