Retrieving XML element name using t-SQL

Question

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.

The current answers here do not take into account Element Names that Repeat in a different Context (have a different XML Path, thus a different meaning). You may want to also check out these answers here that include the Path: stackoverflow.com/questions/2266132/… — MikeTeeVee
– MikeTeeVee, Commented Apr 14, 2019 at 10:02

Matt W · Accepted Answer · 2010-07-05 16:09:19Z

36

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...

answered Jul 5, 2010 at 16:09

Matt W

12.7k29 gold badges141 silver badges244 bronze badges

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

2 Comments

Matt W Over a year ago

FYI, the solution came from stumbling across this post: stackoverflow.com/questions/2266132/…

Cade Roux Over a year ago

Your answer is fine. You may also want to review this column for some useful XML gymnastics: beyondrelational.com/blogs/jacob/archive/2010/05/30/…

Christian Specht · Accepted Answer · 2011-06-23 14:51:07Z

6

DECLARE @xml as xml
SET @xml = '<Address><Home>LINE1</Home></Address>'

SELECT Nodes.Name.query('local-name(.)') FROM @xml.nodes('//*') As Nodes(Name)

This will give the list of all elements

edited Jun 23, 2011 at 14:51

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

answered Jun 23, 2011 at 11:00

Susmeet Khaire

611 silver badge1 bronze badge

Collectives™ on Stack Overflow

Retrieving XML element name using t-SQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related