Decomposing an XML Document into a table of path and values

Suppose that you had an XML Document like:

<a>1<b>2<c>3</c>4</b><d>5</d>6</a>

What would be the best way to decompose it such that you end up with a table like:

XPath Value
/a 123456
/a/b 234
/a/b/c 3
/a/d 6

I eventually want to join this table with another table that has XPath values in a column.

Thanks,
Wells

Here is one solution:

declare @.x xml

set @.x ='<a>1<b>2<c>3</c>4</b><d>5</d>6</a>'

select n.query('

for $node in .

return

for $i in //*[some $j in ./descendant-or-self::* satisfies $j is $node]

return text{concat("/", local-name($i))}

'), n.value('.', 'nvarchar(100)')

from @.x.nodes('//*') t(n)

The result is:

/a 123456
/a/b 234
/a/b/c 3
/a/d 5

|||

Thanks Adrian,

This does the job nicely.

Wells