List available nodes from xml datatype

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 2005 Forums

Transact-SQL (2005)

List available nodes from xml datatype

Author

Topic

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-08-25 : 18:51:50

I am trying to come up with a way to list the nodes available in a xml string to be used similarly to the information_schema.columns view but I am stumped. I have been playing around using different xquery methods but haven't found anything that will get me even close to what Im after.

What I got:

<customer>
    <name>Mike</name>
    <address>123 Anywhere</address>
    <phone>1234567890</phone>
</customer>

What I would like is to be able to provide the parent node, in this case "customer" and receive the following:

name
address
phone

Any ideas? Thanks.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-08-26 : 12:16:51

Ok, Ive made some progress here. I am able to pull the individual nodes using the following:

declare @xml xml
set @xml = '
<customer>
	<name>Mike</name>
	<address>
		<street>123 Anywhere</street>
		<city/>
	</address>
	<phone>1234567890</phone>
</customer>'

select  @xml.query('/child::node()/*[1]')

My problem now is that I havent found a way to specify only to return the name of the node. I have found the xquery function fn:node-name (http://www.xqueryfunctions.com/xq/fn_node-name.html) however I cant seem to get the correct syntax using t-sql.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-08-26 : 12:34:37

More progress:

declare @xml xml
set @xml = '
<customer>
	<name>Mike</name>
	<address>
		<street>123 Anywhere</street>
		<city/>
	</address>
	<phone>1234567890</phone>
</customer>'

select  @xml.query('local-name(/child::node()[1]/child::node()[1])')

output: name

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-08-27 : 00:27:05

try this, it ll give all the nodes

select T.items.value('fn:local-name(.[1])', 'varchar(1000)')
from @xml.nodes('//*') AS
T(items)

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-08-27 : 10:13:46

Great Peter! Exactly what I was looking for.
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources