Xml data covert node values into comma sep string

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)

Xml data covert node values into comma sep string

Author

Topic

mac73
Starting Member

3 Posts

Posted - 2010-04-19 : 10:53:31

Hello,

I am new to sql server xml data type. My requirement is to covert xml nodes values into comma seperate string.

declare

@ProductIDs xml

set

@ProductIDs = '<Products><names><name>Joe</name><name>usha</name><name>jack</name></names><ids><id>12</id><id>14</id></ids></Products>'

SELECT

ParamValues

.ID.query('names/name').value('.','VARCHAR(20)') as AllNames,

ParamValues

.ID.query('ids/id').value('.','VARCHAR(20)') as AllIds

FROM

@ProductIDs.nodes('/Products') as ParamValues(ID)

The result is

Joeushajack and 1214

I want the result to be

Joe,usha,jack and 12,14

How do i achieve that?

Regards

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-04-21 : 03:34:09

One way using FLWOR:

declare @ProductIDs xml 

set @ProductIDs = '<Products><names><name>Joe</name><name>usha</name><name>jack</name></names><ids><id>12</id><id>14</id></ids></Products>'

select	stuff(p.n.query('for $p in names/name/text() return <n>,{$p}</n>').value('.', 'varchar(50)'), 1, 1, '') [nameList],
	stuff(p.n.query('for $p in ids/id/text() return <n>,{$p}</n>').value('.', 'varchar(50)'), 1, 1, '') [idList]
from	@ProductIDs.nodes('Products')p(n)

though, this schema will not help you tie the name:id relationship together. I assume you want to link the names with their respective ids? or maybe not, since you show 3 names and only 2 ids.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources