Tuesday, March 27, 2012

can I get attribute name in SQL XML? thanks

a xml string like

<field name="david" country="ca" age="13" />

in XSL , I can get each attribute name and value by name() and .

So in SQL 2005, if I don't know attribute name in head, how to loop through all attributes and get their name and value?

thanks

declare @.x xml
set @.x = '<field name="david" country="ca" age="13" />'

select x.value('local-name(.)', 'varchar(20)') as Name,
x.value('.', 'varchar(20)') as Value
from @.x.nodes('/field/@.*') as R(x)

No comments:

Post a Comment