column name from xml string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

column name from xml string

Author

Topic

y.holding
Starting Member

2 Posts

Posted - 2011-06-27 : 10:19:42

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.

Michael Holding

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-27 : 10:44:39

You can use the xquery function local-name to get the node names, for example:


declare @x xml;
set @x = 'Put your XML here';

select
   c.value('local-name(.[1])','varchar(max)') as Cols
from
   @x.nodes('//Customer[1]/*') T(c);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-27 : 10:46:47

On second thoughts, it may be better to do it like this in case not all Customer nodes have all the sub-nodes:


declare @x xml;
set @x = 'Put your XML here';

select DISTINCT
   c.value('local-name(.[1])','varchar(max)') as Cols
from
   @x.nodes('//Customer/*') T(c);

But this would be less efficient because it would traverse your entire XML data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources