Reading in CDATA XML to SQL Table

Question

I have a perplexing issue with a "[CDATA]" XML document. I'm trying to create a tabular format for it in SQL Server. Below is a snippet of what the XML looks like:

    ''''
    <root>
    <response success="true">
        <output>
            <![CDATA[Account Name,Account Code,Level Name,"Employee",01/2021,02/2021,03/2021
    "Hours Used","Predicted.HoursUsed","Non-Labor","A, Apple A",261.0,232.0,240.0
    "Hours Used","Predicted.HoursUsed","Non-Labor","B, Orange L",0.0,72.0,368.0
    "Hours Used","Predicted.HoursUsed","Non-Labor","C'Pear, D",0.0,0.0,0.0
    "Hours Used","Predicted.HoursUsed","Non-Labor","D, Grape
    A",302.0,300.5,358.5]]>
        </output>
    </response>
    </root>

    ''''

I would like to transform the above to a table like below:

Account Name	Account Code	Level Name	Employee	01/2021	02/2021	03/2021
Hours Used	Predicted.HoursUsed	Non-Labor	A, Apple A	261.0	0.0	0.0
Hours Used	Predicted.HoursUsed	Non-Labor	B, Orange L	232.0	72.0	0.0
Hours Used	Predicted.HoursUsed	Non-Labor	C'Pear, D	240.0	368.0	0.0

I tried variations using "OPENXML" but it hasn't been working to parse out the data. I'm not sure what else I'm missing. Appreciate the help!

Thank you.

Answer 1

This solution kinda works, except the indexes are static. And for some reason the replace doesn't seem to be working to remove the commas in names.

''''

DECLARE @doc XML
   = '<root>
    <response success="true">
        <output>
            <![CDATA[Account Name,Account Code,Level 
Name,"Employee",01/2021,02/2021,03/2021, 04/2021
    "Hours Used","EAC_Hours.HoursUsed","Unaligned Non-Labor","A, Apple 
A",261.0,232.0,240.0,55.0
    "Hours Used","EAC_Hours.HoursUsed","Unaligned Non-Labor","B, Orange 
L",0.0,72.0,368.0,165.0
    "Hours Used","EAC_Hours.HoursUsed","Unaligned Non-Labor","C''Pear, 
D",0.0,0.0,0.0,0.8
    "Hours Used","EAC_Hours.HoursUsed","Unaligned Non-Labor","D, Grape    
A",302.0,300.5,358.5,188.0]]>
        </output>
    </response>
    </root>';

DECLARE @hnd INT;
DECLARE @string NVARCHAR(1000);


EXEC sp_xml_preparedocument @hnd OUTPUT, @doc;

SELECT *
FROM OPENXML

(@hnd, '/root', 0);

SELECT @string = text
FROM OPENXML(@hnd, '/root', 0)
WHERE nodetype = 3
      AND localname = '#text'
      AND parentid = 4;

--SELECT REPLACE(@string,'"Hours Used"','zzz')
SET @string = REPLACE(@string, '   "', '|"');
SELECT value
FROM STRING_SPLIT(@string, '|');


((Select Cast('<x>' + replace(value,',','</x><x>')+'</x>' as xml) as xDim) AS 
Record
FROM STRING_SPLIT(@string, '|'))
         
EXEC sp_xml_removedocument @hnd;

''''