如何获取 SQLXML 中每个属性的名称、值和父元素名称？

Question

给定这个 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>'

我想检索每个属性，它的值和父元素的名称作为表：

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

Answer 1

我最初的回答（针对我自己的问题）：

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)