Sql-Server: Split Select Values to Multiple Rows, After using REPLACE() Function on Result

Question

I got a table which contains values like

1.<content><value>foo</value><value>bar</value></content>
2.<content><value>value3</value><value>value4</value></content>

I'm using the REPLACE Function, to remove all the Tags (content and value) and to seperate the values I replaced </value> with ;

select CAST(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(CAST(TEXTVALUE as NVarchar(MAX))
,'<content>',''),'<value>','')
,'</value>',';')
,' ','')
,'</content>','')
AS NText) from dbo.mytable ...

The output of my select statement looks like this:

1.foo;bar
2.value3;value4

However - I would like to split this select statement to return a new row for each value:

1. foo
2. bar
3. value3
4. value4

I've seen lots of answers and methods to split the result of a select statement into multiple rows, but I didn't see any way to first replace part of the values and then split the result to multiple rows by character.

I hope you can help me out here.

EDIT: Thanks for so many helpful answers. I forgot to add, that I also would like to be able to select other columns within the same request.

If you are on SQL Server 2016 or newer you can also use the [STRING_SPLIT function](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql). — kirchner, Nov 08 '17 at 12:47

Pரதீப் · Accepted Answer · 2017-11-08T13:30:42.600

1

Here is one way using xml nodes method

SELECT id,Split.a.value('.', 'VARCHAR(100)')
FROM   (SELECT Cast(xmlcol AS XML) AS data,id
        FROM   Yourtable) a
       CROSS apply Data.nodes ('content/value') AS Split(a)

edited Nov 08 '17 at 13:30

answered Nov 08 '17 at 12:51

Pரதீப்

91,748
19
131
172

Splendid. Thanks! If I want to select more than just the xmlcolumn but also the id column from the same table, how should I update this query properly? – Lama Nov 08 '17 at 13:20

score 0 · Answer 2 · answered Nov 08 '17 at 12:50

0

This should do the trick:

SELECT STRING_SPLIT(Sub.NText, ';') FROM 
    (select CAST(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(CAST(TEXTVALUE as NVarchar(MAX))
    ,'<content>',''),'<value>','')
    ,'</value>',';')
    ,' ','')
    ,'</content>','')
    AS NText) from dbo.mytable) AS Sub

answered Nov 08 '17 at 12:50

michal.jakubeczy

8,221
1
59
63

1

Requires SQL Server 2016+ -- we should ask the OP what version they're using. – Aaron Bertrand Nov 08 '17 at 12:59
if you're not on SQL Server 2016 check this post - https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows – michal.jakubeczy Nov 08 '17 at 13:17

Nilesh Vishwakarma · Answer 3 · 2017-11-08T13:27:19.780

Add XML string inside of root element. like this

<root><content><value>foo</value><value>bar</value></content><content><value>value3</value><value>value4</value></content></root>

declare @xmlPath xml = '<root><content><value>foo</value><value>bar</value></content><content><value>value3</value><value>value4</value></content></root>';
SELECT 
    p.value('(text())[1]', 'VARCHAR(8000)') AS value
FROM @xmlPath.nodes('/root/content/value') AS a(p)

no need to replacing XML element string.

also u can use this. if you are fetching value into table. textstring==is field name

SELECT  
    p.value('(text())[1]', 'VARCHAR(8000)') AS value
FROM #tempTable temp
 CROSS APPLY  textstring.nodes('/content/value') AS a(p)

Sql-Server: Split Select Values to Multiple Rows, After using REPLACE() Function on Result

3 Answers3