Zerlegen von Textinhalten mit Trennzeichen in mehrere Zeilen

CREATE TABLE ZerlegenText (

id int,

Inhalt nvarchar(255)

)

INSERT INTO ZerlegenText

SELECT

1,’TextA;TextB;TextC’

Zielview:

1 TextA

1 TextB

1 TextC

SELECT ID, Members.Member.value(‘.’,’VARCHAR(8000)’) Text
FROM
(–Convert delimited string to XML
SELECT ID, CAST(‘<Inhalt><Text>’
+ REPLACE(Inhalt, ‘;’ , ‘</Text><Text>’)
+ ‘</Text></Inhalt>’ AS XML) AS tempPlayer
FROM ZerlegenText ) AS tempPlayer
CROSS APPLY tempPlayer.nodes(‘/Inhalt/Text’) Members(Member)