SQL Server convert xml to csv using CTE - nulls currently being ignored

Question

I am using CTE to convert xml to csv so that it can be exported to a file, however if I have an empty xml tag, this currently gets ignored. Here is my initial solution courtesy of this previous very helpful post: https://stackoverflow.com/a/23785202/6260721

Here is my sql:

CREATE TABLE EXPORT_TEST
    (
    DATA varchar(max)
    )

INSERT INTO EXPORT_TEST (DATA)
VALUES ('<EXPORT_DATA><ID>ABC123</ID><PRICE_A>5.6</PRICE_A><PRICE_B></PRICE_B><PRICE_C>8.1</PRICE_C></EXPORT_DATA>')

DECLARE @commaSeparatedValues NVARCHAR(MAX)

DECLARE @xml XML = (SELECT TOP 1 CONVERT(xml,DATA) FROM EXPORT_TEST)

;WITH cte AS (
    SELECT 
        rownr = ROW_NUMBER() OVER (ORDER BY @commaSeparatedValues),
        Tbl.col.query('.') AS [xml]
    FROM @xml.nodes('EXPORT_DATA') Tbl(col)
), cols AS (
    SELECT
        rownr,
        Tbl.Col.value('.', 'nvarchar(max)') AS Value
    FROM cte
    CROSS APPLY cte.xml.nodes('//text()') Tbl(Col) 
)
INSERT INTO EXPORT_TEST(DATA)
SELECT DISTINCT
     STUFF((
       SELECT ',' + IIF(ISNUMERIC(value) = 1, Value, '''' + Value + '''')
       FROM cols SSF WHERE SSF.rownr = S.rownr
       FOR XML PATH(''),TYPE
       ).value('.','VARCHAR(MAX)'
     ), 1, 1, '') as DATA
    FROM cols S

SELECT * FROM EXPORT_TEST

At the moment, it is returning:

'ABC123',5.6,8.1

But I don't want it to ignore PRICE_B, I want it to return an empty string:

'ABC123',5.6,,8.1  <--extra comma required where PRICE_B should be

How can I achieve this?

Answer 1

Besides the possibility to shredd the full XML and re-concatenate its values (there is an answer already), you might use FLWOR-XQuery:

DECLARE @xml XML=
'<EXPORT_DATA>
  <ID>ABC123</ID>
  <PRICE_A>5.6</PRICE_A>
  <PRICE_B />
  <PRICE_C>8.1</PRICE_C>
</EXPORT_DATA>';

EDIT better to read with a variable $txt instead of ($n/text())[1]

SELECT 
    STUFF
    (
        @xml.query('
        let $r:=/EXPORT_DATA
            for $n in $r/*
                let $txt:=($n/text())[1]
                return if(empty($txt) or not(empty(number($txt)))) then
                           concat(",",string($txt))
                       else concat(",''",string($txt),"''")
        ').value('.','nvarchar(max)'),1,1,'');

The result

'ABC123' ,5.6 , ,8.1

Answer 2

This code works on a mass of records using XQUERY.

• I'm assuming char(10) (Line Feed) does not appear in your data.
• I'm assuming the maximum length of the concatenated text is 1000 (I don't want to use varchar(max) for no good reason)

You can change both of these assumptions if you wish

---

declare @separator char(1) = char(10)

select  substring
        (
            replace
            (
                cast
                (
                    cast(DATA as xml).query
                    (
                       'for     $i in //* 
                        where   not($i/*) 
                        return  concat
                                (
                                    sql:variable("@separator")
                                   ,if(local-name($i) = "ID") then ('''''''') else ('''')
                                   ,($i/text())[1]
                                   ,if(local-name($i) = "ID") then ('''''''') else ('''')
                                )'
                    ) as nvarchar(1000)
                ) ,' ' + @separator ,','
            ) ,2 ,1000
        ) as csv

from    EXPORT_TEST

---

INSERT INTO EXPORT_TEST (DATA) VALUES
 ('<EXPORT_DATA><ID>ABC123</ID><PRICE_A>5.6</PRICE_A><PRICE_B></PRICE_B><PRICE_C>8.1</PRICE_C></EXPORT_DATA>')
,('<EXPORT_DATA><ID>DEF456</ID><PRICE_A>6.7</PRICE_A><PRICE_B>66.77</PRICE_B><PRICE_C>7.2</PRICE_C></EXPORT_DATA>')
,('<EXPORT_DATA><ID>GHI789</ID><PRICE_A></PRICE_A><PRICE_B>88.99</PRICE_B><PRICE_C></PRICE_C></EXPORT_DATA>')

---

csv
'ABC123',5.6,,8.1
'DEF456',6.7,66.77,7.2
'GHI789',,88.99,