正在从CDATA XML读取SQL表

Question

我有一个令人困惑的问题；[CDATA]"XML文档。我正试图在SQL Server中为它创建一个表格格式。下面是XML的一个片段：

''''
<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>
''''

我想把上面的转换成下面的表格：

级别名称员工2021年1月<2021年2月>2021年3月A，苹果A>232.0>[/tr>240.0>/tbody>

账户名称		账户代码
使用小时数	预测。使用小时数		非人工	261.0	0.0	0.0
使用小时数		预测。使用小时数		非人工	B，橙色L	22.0	0.0
使用小时数		预测。使用小时数		非人工	C’Per，D	368.0	0.0

除了索引是静态的之外，这个解决方案有点工作。出于某种原因，替换似乎无法删除名称中的逗号。

''

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;

''