xml-like string to individual rows

Question

xml-like string to individual rows

Mindy Hreczuck

SSCommitted

Points: 1796
More actions
October 12, 2011 at 11:46 am

#249870

I have columnA with a single value and columnB with strings in it such as:
<ITEM>Jane, Mary</ITEM> <ITEM> Smith, Joe</ITEM> <ITEM>Doe, Jane</ITEM>
I've been trying to parse it out with patindex or charindex to substring out each name so I can insert them into a reporting table, eg. substring(columnA,@start,@end). But I am having a difficult time figuring out how to move @start and @end around the tags. I can't seem to get past breaking out the first two names. Is there a better way to do this?

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Here's a solution using XQuery to parse the (obviously) xml data. Since I don'T know the data type of the related column, I used a cte to convert it into an xml column.

Also please note how I posted the sample data in a ready to use format. That makes it a lot easier for those of us trying to help you...

DECLARE @tbl TABLE (id INT, val VARCHAR(300))

INSERT INTO @tbl

SELECT 1,'<ITEM>Jane, Mary</ITEM> <ITEM> Smith, Joe</ITEM> <ITEM>Doe, Jane</ITEM>'

;

WITH cte AS

(

SELECT

id, CAST(val AS XML) AS x

FROM @tbl

)

SELECT id,

c.value('.','varchar(30)') AS each_name

FROM cte

CROSS APPLY x.nodes('/ITEM') T(c)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mindy Hreczuck SSCommitted Points: 1796 More actions · Answer 2

Thanks so much! There's a lot in there to wrap my head around, I'm still learning the xml manipulations. I hadn't provided any further info as it didn't matter to the problem. I didn't need to do any tweaking to your example for it to work, perfect. Thanks again.