使用SQL XPATH查询获取XML元素名称和属性值

Given an XML type string such as

给定一个XML类型字符串，如

declare @xml xml

SET @xml = 
'<PO>
  <Amount type="approved">10.00</Amount>
  <Year type="">2013</Year>
  <GeneralNotes>
    <Note>
      <NoteText type="instruction">CallVendor</NoteText>
      <Date type="">1-1-2013</Date>
    </Note>
    <Note type="">
      <NoteText type="instruction">ShipNow</NoteText>
      <Date type="">2-2-2013</Date>
    </Note>
  </GeneralNotes>
</PO>'

I want to get every element and its attribute if it has one. My desired output (without duplicates) is

我希望获得每个元素及其属性（如果有的话）。我想要的输出（没有重复）是

ElementName   ElementAttribute

PO  
Amount   approved
Note     instruction

I've tried code similar to this line

我尝试过类似这行的代码

SELECT T.doc.query('fn:local-name(.)') 
FROM @xml.nodes('PO//*[1]') AS T(doc)

This brings in duplicates and I am not sure how to select the attribute value. I only need the first occurrence (i.e,. GeneralNotes/Note[1]). I have a much large file with many other element names so I do not want to parse them out individually.

这带来了重复，我不知道如何选择属性值。我只需要第一次出现（即，GeneralNotes / Note [1]）。我有一个包含许多其他元素名称的大文件，因此我不想单独解析它们。

2 个解决方案

#1

SELECT DISTINCT
       T.doc.value('fn:local-name(..)[1]', 'nvarchar(max)') as ElementName,
       T.doc.value('.', 'nvarchar(max)') as ElementAttribute 
FROM @xml.nodes('PO//@*[1]') AS T(doc)
WHERE T.doc.value('.', 'nvarchar(max)') <> ''

Result:

结果：

ElementName     ElementAttribute
--------------- ----------------
Amount          approved
NoteText        instruction

#2

select distinct
    a.c.value('local-name(..)', 'nvarchar(max)') as ElementName,
    a.c.value('.', 'nvarchar(max)') as ElementAttribute
from @xml.nodes('//@*[. != ""]') as a(c)

sql fiddle demo

sql小提琴演示