views:

34

answers:

1

If I have:

<quotes>
  <quote>
    <name>john</name>
    <content>something or other</content>
  </quote>
  <quote>
    <name>mary</name>
    <content>random stuff</content>
  </quote>
</quotes>

How do I get a list of the element names 'name' and 'content' using T-SQL?

The best I've got so far is:

declare @xml xml
set @xml = ...
select r.value('quotes/name()[1]', 'nvarchar(20)' as ElementName
from @xml.nodes('/quotes') as records(r)

But, of course, I can't get this to work.

+1  A: 

Actually, sorry, the best I've got is:

select distinct r.value('fn:local-name(.)', 'nvarchar(50)') as t
FROM
    @xml.nodes('//quotes/*/*') AS records(r)

Guess I answered my own question...

Matt W
FYI, the solution came from stumbling across this post: http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server
Matt W
Your answer is fine. You may also want to review this column for some useful XML gymnastics: http://beyondrelational.com/blogs/jacob/archive/2010/05/30/select-from-xml.aspx
Cade Roux