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?

Do you really need the quotes around the text column? Not very conventional — David דודו Markovitz, Dec 21 '16 at 13:50
This looks painful. Why use SQL for this? SQL is a special purpose language designed for DDL/DML database operations. Unless the XML was stored within a database, one perfect solution is [XSLT](http://www.w3.org/Style/XSL/) also a special-purpose, declarative language designed specifically to manipulate XML. And yes, it can [render CSV](http://stackoverflow.com/questions/365312/xml-to-csv-using-xslt) and other delimited TXT with ease! — Parfait, Dec 21 '16 at 14:25

David דודו Markovitz · Answer 1 · 2016-12-21T15:40:22.020

1

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,

edited Dec 21 '16 at 15:40

answered Dec 21 '16 at 13:36

David דודו Markovitz

42,900
6
64
88

Close, but you didn't handle the qoutes for non-numeric values :-) – Shnugo Dec 21 '16 at 13:47
@Shnugo, Ooch :-) (Although I've never seen a CSV that actually did that) – David דודו Markovitz Dec 21 '16 at 13:49
Yours could be simplified to `SELECT REPLACE(@xml.query('data(/EXPORT_DATA/*)').value('.','nvarchar(max)'),' ',',')` This works, as long as there is no *blank* in textual data. Why ever, the function `data()` knows nothing else then the blank as separator. That's a pitty... – Shnugo Dec 21 '16 at 13:51
*Although I've never seen a CSV that actually did that*, well, yes... Whenever you expect your delimiter being contained in textual values... – Shnugo Dec 21 '16 at 13:59
@Shnugo - I obviously didn't count on spaces not to appear within the data and this is why I used a separator which will most likely won't appear in the data – David דודו Markovitz Dec 21 '16 at 14:04
I didn't get your last comment – David דודו Markovitz Dec 21 '16 at 14:05
Just imagine a value like `I need this, this and that` as part of a CSV file with the comma as delimiter. Without quotes around textual content, this would break the structure, due to the comma in the middle. Either quotes or escapes... – Shnugo Dec 21 '16 at 14:07
Anyway, the other answer's got the acceptance, but I think we should not stay empty. +1 for your nice answer! – Shnugo Dec 21 '16 at 14:08
@Shnugo, lost focus for a moment... I'm used to `"` – David דודו Markovitz Dec 21 '16 at 14:15

Shnugo · Answer 2 · 2016-12-21T14:12:27.930

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

edited Dec 21 '16 at 14:12

answered Dec 21 '16 at 13:38

Shnugo

66,100
9
53
114

Close, but you didn't handle the extra spaces :-) – David דודו Markovitz Dec 21 '16 at 13:45
@DuduMarkovitz, I think, the easiest for this was to wrap the whole call in a `REPLACE(this,' ,',',')` to replace each *blank+comma* with just a comma... – Shnugo Dec 21 '16 at 13:53
@DuduMarkovitz, One last thought... Now I think, one should use something *sure* within the `concat()`, something like `##dlmt##`, and replace this with a plain comma at the end... – Shnugo Dec 21 '16 at 14:11
If you trust ` ,` not the appear in a text, as in `text ,as in` :-) – David דודו Markovitz Dec 21 '16 at 14:12
This is exactly what I did. I used Line Feed but this will work for any string. You can see that I'm using `replace (...,' '+@separator,',')` – David דודו Markovitz Dec 21 '16 at 14:19

gotqn · Accepted Answer · 2016-12-21T12:58:27.207

What about this:

;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('EXPORT_DATA/child::node()') Tbl(Col) 
)
INSERT INTO EXPORT_TEST(DATA)
SELECT DISTINCT
     STUFF((
       SELECT ',' + IIF(ISNUMERIC(value) = 1 OR LEN(value) = 0, Value, '''' + Value + '''')
       FROM cols SSF WHERE SSF.rownr = S.rownr
       FOR XML PATH(''),TYPE
       ).value('.','VARCHAR(MAX)'
     ), 1, 1, '') as DATA
    FROM cols S

Using cte.xml.nodes('EXPORT_DATA/child::node()') in the second CTE will give as all nodes:

;WITH cte AS (
    SELECT 
        rownr = ROW_NUMBER() OVER (ORDER BY @commaSeparatedValues),
        Tbl.col.query('.') AS [xml]
    FROM @xml.nodes('EXPORT_DATA') Tbl(col)
)
    SELECT
        rownr
        ,Tbl.Col.query('.')
        ,Tbl.Col.value('.', 'nvarchar(max)') AS Value
    FROM cte
    CROSS APPLY cte.xml.nodes('EXPORT_DATA/child::node()') Tbl(Col)

Then, in the concatenation we need to add check for empty string:

IIF(ISNUMERIC(value) = 1 OR LEN(value) = 0, Value, '''' + Value + '''')

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

3 Answers3

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

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