CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsqlxml
rguy
rguy

Reputation: 135

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

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.

Upvotes: 5

Views: 4726

Answers (2)

roman
roman

Reputation: 117345

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 3

Related Questions