String to Rows question (quasi xml??)

Question

Post reply

String to Rows question (quasi xml??)

Mindy Hreczuck

SSCommitted

Points: 1796
More actions
December 6, 2012 at 12:35 pm

#265867

I haven't been able to apply the xml solutions to the kind of string I have in my data. I am looking to convert a single row to multiple rows. My row looks like this and is datatype text:
<ITEM>ABC</ITEM><ITEM>DEF</ITEM><ITEM>GHI</ITEM>
To result in:
ABC
DEF
GHI
I can do it the hard way with replacements and variables, but suspect there's a more graceful way.

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm not sure if you have tried something like this or you might be looking for something else.

CREATE TABLE #Test( string varchar(1000))

INSERT INTO #Test VALUES('<ITEM>ABC</ITEM><ITEM>DEF</ITEM><ITEM>GHI</ITEM>')

SELECT item

FROM #Test t

CROSS APPLY dbo.DelimitedSplit8K( REPLACE( REPLACE( t.string, '</ITEM>', ','), '<ITEM>', ''), ',') d

WHERE item > ''

DROP TABLE #Test

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

Perhaps you're looking for something like this.

CREATE TABLE #Test( string TEXT)

INSERT INTO #Test

VALUES('<ITEM>ABC</ITEM><ITEM>DEF</ITEM><ITEM>GHI</ITEM>')

SELECT Item=item.value('.', 'VARCHAR(10)')

FROM #Test

CROSS APPLY (SELECT CAST(string AS XML)) a(string1)

CROSS APPLY string1.nodes('ITEM') b(item)

DROP TABLE #Test

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St