Get XML element name and attribute value using SQL XPATH query

Question

For an XML type string such as

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 each element and its attribute, if any. My desired result (no duplicates) -

ElementName   ElementAttribute

PO  
Amount   approved
Note     instruction

I tried code similar to this line

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

This leads to duplication, and I'm not sure how to select the attribute value. I need only the first occurrence (i.e. GeneralNotes/Note[1]). I have a large file with many other element names, so I don't want to parse them separately.

Answer 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

Answer 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