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

Transact-SQL (2005)

sql 2005 XML

Author

Topic

preets
Starting Member

1 Post

Posted - 2009-11-14 : 13:59:32

<SampleXML>
<Tables>
<Table value="abc" />
<Table value="123" />
</Tables>
<Fields>
<Field value="Name" />
<Field value="Price" />
</Fields>
</SampleXML>

I need to parse the above xml such that it returns the attributes in a flat
format,
e.g. abc,123
How can this be achieved using sql 2005

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-15 : 01:27:19

http://blogs.msdn.com/mrorke/

liang.??
Starting Member

3 Posts

Posted - 2009-11-15 : 23:56:09

declare @x xml;
set @x = '<SampleXML>
<Tables>
<Table value="abc" />
<Table value="123" />
</Tables>
<Fields>
<Field value="Name" />
<Field value="Price" />
</Fields>
</SampleXML>'

select STUFF(CAST(@x.query('for $i in /SampleXML/Tables/Table
return concat(",",string($i/@value))') AS varchar(MAX)),1,1,'');

declare @d xml;
set @d = (
select ','+x
from (
select T.x.value('@value','varchar(20)') AS x
from @x.nodes('/SampleXML/Tables/Table') AS T(x)
) as t
for xml path(''));

select stuff(cast(@d as varchar(MAX)),1,1,'')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources