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

  • 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.)

     

     

     

    Friday, June 11, 2010 4:52 PM

Answers

  • 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
    • Proposed as answer by Kirill Polishchuk Friday, June 11, 2010 6:32 PM
    • Marked as answer by dbixby001 Friday, June 11, 2010 6:48 PM
    Friday, June 11, 2010 6:31 PM

All replies

  • 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
    • Proposed as answer by Kirill Polishchuk Friday, June 11, 2010 6:32 PM
    • Marked as answer by dbixby001 Friday, June 11, 2010 6:48 PM
    Friday, June 11, 2010 6:31 PM
  • No need for expensive FLWOR expression:

    select a.b.value('fn:local-name(.)[1]', 'varchar(10)')
    from @xml.nodes('Root/Row[1]/*') a(b)
    Monday, June 14, 2010 9:51 PM
    Answerer