How to get every attribute's name, value and parent element name in SQLXML?

Question

Given this SQL:

DECLARE @content XML
SET @content =
'<people>
  <person id="1" bimble="1">
    <firstname bobble="gomble">John</firstname>
    <surname>Doe</surname>
  </person>
  <person id="2" bimble="11">
    <firstname bobble="zoom">Mary</firstname>
    <surname>Jane</surname>
  </person>
  <person id="4" bimble="10">
    <firstname bobble="womble">Matt</firstname>
    <surname>Spanner</surname>
  </person>
</people>'

I want to retrieve every attribute, it's value and parent element's name as a table:

Parent Name Attribute Name Attribute Value
----------- -------------- ---------------
person      id             1
person      bimble         1
firstname   bobble         gomble
person      id             2
person      bimble         11
firstname   bobble         zoom
person      id             4
person      bimble         10
firstname   bobble         womble

Matt W · Accepted Answer · 2010-07-07 10:44:27Z

6

My initial answer (to my own question):

SELECT
    elem.value('local-name(..)', 'nvarchar(10)') AS 'Parent Name',
    elem.value('local-name(.)', 'nvarchar(10)') AS 'Attribute Name',
    elem.value('.', 'nvarchar(10)') AS 'Attribute Value'
FROM
    @content.nodes('//@*') AS El(elem)

answered Jul 7, 2010 at 10:44

Matt W

12.7k29 gold badges141 silver badges244 bronze badges

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

2 Comments

Andomar Over a year ago

Why not add this at the bottom the question?

Matt W Over a year ago

Because it's an answer and the system marks you down for not having a selected answer.

Collectives™ on Stack Overflow

How to get every attribute's name, value and parent element name in SQLXML?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related