Get XML element name and the attribute value using SQL XPATH query

Question

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

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