Cross Applying to create new rows with "Nodes"

Question

Each field (we'll call it field1) has a concatenated string associated with it (we'll call it field2). I want to delimit field2 so the concatenated value break out into several rows. I'm using "Nodes" to accomplish this but I keep getting an error.

There are other methods to achieve this goal such breaking the string into columns and pivoting on the data but that's cumbersome. This method seems the most efficient but I don't quite know how to do it.

with xmll as ( 
select field1, cast('<r>'+replace(replace(field2,'Case #',''), ',', '</r> 
<r>')+'</r>' as xml) as XMLCol
from [database].dbo.table i
where field2 like 'Case%#%'
)

select x.*
, n.r.value('.', 'varchar(max)')
from xmll x

cross apply 
XMLCol.nodes(XMLCol) as n(r)

order by field1

The current error message is:

Msg 8172, Level 16, State 1, Line 1
The argument 1 of the XML data type method "nodes" must be a string literal.

What it is now:

What I want:

string splitting in SQL Server 2017 or higher should be done using the built in function. In lower versions, read Aaron Bertrand's [Split strings the right way – or the next best way](https://sqlperformance.com/2012/07/t-sql-queries/split-strings) — Zohar Peled, Jul 12 '19 at 06:55
Just to add to @ZoharPeled: Even better than the *built-in* `STRING_SPLIT()` is a little hack with `OPENJSON`. This is better, because `OPENJSON` will return the part's index, while `STRING_SPLIT()` does not guarantee to return the given order... — Shnugo, Jul 12 '19 at 08:12
@Shnugo Yes, the order is not guaranteed out of the box, but look at [this blog post](http://ariely.info/Blog/tabid/83/EntryId/223/T-SQL-Playing-with-STRING_SPLIT-function.aspx) by Ronen Ariely - where he shows how to get the correct order back. — Zohar Peled, Jul 12 '19 at 08:29
@ZoharPeled Okay... So they provide a function to make string-spltting easy and we then need a full page of code with a very slow repeating `CHARINDEX` to overcome the missing sort order... well... uhm... I prefer `SELECT A.[key] AS Position,A.[value] AS Part FROM OPENJSON('[1,2,3]') A` or `'["hallo","this","is","cool"]'` for non-int arrays. This is lightning fast and does not need any hacks. Any string can easily be transformed with `CONCAT('[',REPLACE('hello this is cool',' ','","'),']')`. And let's hope for a correction of `STRING_SPLIT()`... — Shnugo, Jul 12 '19 at 10:12
For anyone interested: [In this post](https://stackoverflow.com/a/38275075/5089204) I show how to pick a part by its index and how to use this as a *position safe* string splitter. And [in this post](https://stackoverflow.com/a/56617711/5089204) I tested, that this approach is much faster than the well known `delimitedsplit8k()` (Jeff Moden modified). — Shnugo, Jul 12 '19 at 10:26
Sorry, the code above is missing some quotes: `CONCAT('["',REPLACE('hello this is cool',' ','","'),'"]')` — Shnugo, Jul 12 '19 at 11:40
@Shnugo Ronen writes at the end of the post it's not a practical solution. The json solution is probably better when the order matters. — Zohar Peled, Jul 12 '19 at 13:39

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

I think you must change this

cross apply 
XMLCol.nodes(XMLCol) as n(r)

to this

cross apply 
XMLCol.nodes('/r') as n(r)

The reason:

Your xml consists of repeated <r> elements. You must tell .nodes() what you want to get back.

For SQL-Server 2016+

If you have got a SQL-Server 2016 or a higher version, I recommend to read the JSON-section of this answer. There are much better approaches to split a string now...

Cross Applying to create new rows with "Nodes"

1 Answers1

For SQL-Server 2016+