Replace empty string with NULL in XML variable

Question

I wish this query would return NULL instead of empty string.

declare @str varchar(max)='A,,C;D,E,F;X,Y,Z'; -- please notice missing B
declare @xmlstr XML
set @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)

SELECT
     t.n.value('col[1]','nvarchar(max)') as Col1
    ,t.n.value('col[2]','nvarchar(max)') as Col2
    ,t.n.value('col[3]','nvarchar(max)') as Col3
FROM
    @xmlstr.nodes ('/rows/row') AS t(n)

This example comes from https://stackoverflow.com/a/39752683/1903793

From the answer: SQL split string and get NULL values instead of empty string I know it can be fixed by easy wrap with NULLIF function:

SELECT
     nullif(t.n.value('col[1]','nvarchar(max)'),'') as Col1
    ,nullif(t.n.value('col[2]','nvarchar(max)'),'') as Col2
    ,nullif(t.n.value('col[3]','nvarchar(max)'),'') as Col3

However I wonder if it might be alternatively fixed by manipulating with XML variable directly, not afterwards.

Note. My question follows SQL split string and get NULL values instead of empty string Please do not mark it as dupe because I have not received answer for XML method.

Why do you think it doesn't already behave like this? `SELECT * FROM dbo.SplitStrings_XML('1,2,,3', ',')` will return a table with a `NULL` for the third row. You actually have to add `ISNULL` to get it to behave the opposite way. If you could include an example of an actual call and the desired output in this question itself, that might help. — Jeroen Mostert, Jan 29 '19 at 16:09
Yeah, now it's *completely* different. The XML variable still does not need "fixing", though, you can just use `col[1]/text()[1]`, `col[2]/text()[1]` for the value selectors (i.e. same approach as the previous version: select the `text()`). In the version as it is now I'm pretty sure you *can't* do this by tweaking the XML alone, because `col[2]` is only going to be `NULL` if there is no second `col` node, and if there is no such node then logically there cannot be a `col[3]` either. — Jeroen Mostert, Jan 29 '19 at 20:55
@JeroenMostert Can you please put it as an answer with code structured? I cannot grasp the solution from your comment. — Przemyslaw Remin, Jan 30 '19 at 06:59
I mean replacing `t.n.value('col[2]','nvarchar(max)') as Col2` with `t.n.value('col[2]/text()[1]','nvarchar(max)') as Col2` (and similarly for the other columns). It's just not that interesting as an answer; I'd say `NULLIF` is fine as well. — Jeroen Mostert, Jan 30 '19 at 08:50
What is supposed `/text()[1]` do? What is this strange construction doing? — Przemyslaw Remin, Jan 30 '19 at 09:08
`col[2]` is the second `col` node. `col[2]/text()` is the text node within `col[2]` (i.e. its contents, or `NULL` if it has no contents). `col[2]/text()[1]` is a technicality necessary for `value()` -- what we got back is actually a sequence of nodes, but `value()` wants only one node, so we select the first (and also only) one. Consult online material on XPath for more. — Jeroen Mostert, Jan 30 '19 at 09:45
@PrzemyslawRemin You might read [this answer](https://stackoverflow.com/a/43242238/5089204) to find more about `text()`. — Shnugo, Feb 02 '19 at 19:04

score 0 · Accepted Answer · answered Feb 14 '19 at 09:40

The credit for this answer is entirely to Jeroen Mostert. For details please see his comments.

declare @str varchar(max)='A,,C;D,E,F;X,Y,Z'; -- please notice missing B
declare @xmlstr XML
set @xmlstr = CAST(('<rows><row><col>' + REPLACE(REPLACE(@str,';','</col></row><row><col>'),',','</col><col>') + '</col></row></rows>') AS XML)

SELECT
     t.n.value('col[1]/text()[1]','nvarchar(max)') as Col1
    ,t.n.value('col[2]/text()[1]','nvarchar(max)') as Col2
    ,t.n.value('col[3]/text()[1]','nvarchar(max)') as Col3
FROM
    @xmlstr.nodes ('/rows/row') AS t(n)

Replace empty string with NULL in XML variable

1 Answers1