Replace empty string with NULL in XML variable

2019-08-18 23:59发布

问题:

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.

回答1:

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)