locked
column name from xml string RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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