locked
column name from xml string RRS feed

  • Question

  • Hi,
    In one of the tables has xmltype datatype columns and this column having below xml data.
    <Customers>
    <Customer>
    <FirstName>Kevin</FirstName>
    <LastName>Goff</LastName>
    <City>Camp Hill</City>
    </Customer>
    <Customer>
    <FirstName>Steve</FirstName>
    <LastName>Goff</LastName>
    <City> Philadelphia </City>
    </Customer>
    </Customers>

    From the string, i want to only column name from these string.

    sample output :

    FirstName
    LastName
    City.


    Thanks,

    Michael Holding


    Michael Holding
    Monday, June 27, 2011 2:32 PM

Answers

  • Try

    DECLARE @xml as xml SET @xml = '<Customers> 
    <Customer> 
    <FirstName>Kevin</FirstName> 
    <LastName>Goff</LastName> 
    <City>Camp Hill</City> 
    </Customer> 
    <Customer> 
    <FirstName>Steve</FirstName> 
    <LastName>Goff</LastName> 
    <City> Philadelphia </City> 
    </Customer> 
    </Customers>' 
    
    select distinct r.value('fn:local-name(.)', 'nvarchar(50)') as ColumnName FROM   @xml.nodes('//Customers/Customer/*') AS records(r) 

     

    • Marked as answer by KJian_ Monday, July 4, 2011 2:05 AM
    Monday, June 27, 2011 3:42 PM