How can I return the "Columns headers" of data from an xml file?

Question

I am looking for a way to read the "column headers" of data available from an xml file (as opposed to the data itself).  How do I do that in sql? For example, in the xml below, I want a sql query that will return "ID", "Color", and "Texture" (rather than "512","Red","Smooth").  How do I do that? Can someone provide sample sql code?

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root smlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Row>
    <ID>512</ID>
    <Color>Red</Color>
    <Texture>Smooth</Texture>
  </Row>
  <Row>
    <ID>513</ID>
    <Color>Blue</Color>
    <Texture>Rough</Texture>
  </Row>
</Root>

(Excuse me for my lack of proper xml terminology above. That may be why I am not able to find my answer in searches.)

 

 

 

Answer 1

declare @xml xml
set @xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Row>
  <ID>512</ID>
  <Color>Red</Color>
  <Texture>Smooth</Texture>
 </Row>
 <Row>
  <ID>513</ID>
  <Color>Blue</Color>
  <Texture>Rough</Texture>
 </Row>
</Root>'

select a.b.value('
for $i in (.)
return fn:local-name($i)[1]', 
'varchar(10)')
from @xml.nodes('Root/Row[1]/*') a(b)

---

Regards

Answer 2

No need for expensive FLWOR expression:

select a.b.value('fn:local-name(.)[1]', 'varchar(10)')
from @xml.nodes('Root/Row[1]/*') a(b)