Comma separated lists into XML

Question

Comma separated lists into XML

brendan woulfe

Hall of Fame

Points: 3888
More actions
June 28, 2011 at 9:14 am

#252736

I'm having some difficulties with some data that I need to return in a big select list. The data is stored as a varchar datatype in comma separated lists and I have to convert it to XML, which I'll admit is something I don't use often. I've approached this problem from several different angles and even found some split functions online that seem to get me half way there but I can't seem to get it right. I'm thinking there's a better way of doing this. Here's the example data below and my expected output. Thanks for any and all help!! 🙂
CREATE TABLE #Table1(id int not null,a varchar(20) not null,b varchar(20) not null)
INSERT INTO #Table1(id,a,b)
SELECT '100','abc,abc','123,456'
UNION ALL
SELECT '100','abc','789'
UNION ALL
SELECT '100','def','321'
UNION ALL
SELECT '101','abc,abc','765,234'
Just column a and column b above need to be in the xml file. The id field is already being picked up by my select statement. I'm just showing it for sample purposes. Here's the sample xml.
<doc>
<a>abc</a>
123
456
789
<a>def</a>
321
</doc>
The results of my select statement should look something like this.
SELECT 100 AS id,'<doc>
<a>abc</a>
123
456
789
<a>def</a>
321
</doc>' AS XMLFile
UNION ALL
SELECT 101 AS id,'<doc>
<a>abc</a>
765
234
</doc>' AS XMLFile
Columns a and b will always have the same number of items in the lists. So if column a has one item (no commas obviously) column b will have one item and there can be any number of items in that list. It won't always be one or two items. I also need a distinct list of values from columns a and b.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

The first trick is splitting the comma-separated values into separate rows. Check out Jeff Moden's article on this site for the best solutions for that.

After that, it should be a relatively easy "for xml auto" query.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 2

I think I got it to work with a scalar function. Just running into a one-to-many relationship problem now, which I should be able to resolve. Thanks for the response! 😀