如何在SQL XML中使用CDATA [英] How to use CDATA in SQL XML
问题描述
请有人帮我提供XML输出模板。客户端已要求我创建xml输出文件。然后，该文件将输入客户的CRM。因此，它必须与客户请求的模板完全匹配。我已经成功地将它与CDATA完美地匹配了几个领域。
Could someone please help me with an XML output template please. I have been requested by a client to create an xml output file. This file will then feed in to Client's CRM. So that's why, it has to be an exact match of Client's requested template. I have managed to match it perfectly apart from CDATA for couple of fields.
以下是可用于测试目的的查询。我需要CDATA包装为客户和区域字段。我还附加了通过以下代码运行得到的输出
below is the query you can use for testing purposes. I need CDATA wrap for fields Client and Area. I have also attached the output i am getting by running below code
If OBJECT_ID('tempdb..#Temp') is Not NULL
Drop table #Temp
CREATE TABLE #Temp
(
[ShiftDate] [date] NULL,
[Ref_Num] [varchar](20) NULL,
[Agency_Worker_Name] [varchar](100) NULL,
[Client] [varchar](100) NULL,
[Area] [VarChar] (100) Null,
[Assignment] [varchar](20) NULL,
[Contract_Start] [varchar](30) NULL,
[Contract_End] [varchar](30) NULL,
[Contract_BreakInMinutes] [varchar](10) NULL,
[Contract_Total] [varchar](30) NULL,
[Actual_Start] [varchar](30) NULL,
[Actual_End] [varchar](30) NULL,
[Actual_BreakInMinutes] [varchar](10) NULL,
[Actual_Total] [varchar](30) NULL,
[Commission] [decimal](18, 2) NULL,
[Total_Cost] [decimal](18, 2) NULL,
[Rate] [varchar](20) NULL,
[OverallCost] [decimal](18, 2) NULL,
[AgencybackingReport] [int] NULL,
[AccountCode] [varchar](20) NULL
)
Insert Into #Temp
Values
('2018-07-24',
'83076641',
'ABCD',
'ABCD',
'ABCD',
'CPA00',
'09:00',
'17:00',
'30',
'07:30',
'10:30',
'17:00',
'30',
'05:30',
'28.49',
'159.01',
'Basic',
'221.59',
'1220883',
' ABCD')
Declare @xml Int=(Select max(AgencyBackingReport) From #Temp)
select @xml As [@AgencyBackingReport],(Select
[Ref_Num] As [@reference],
[ShiftDate] as [@startdate],
Case When [AccountCode] is NULL Then 'Unknown' Else [AccountCode] End as [@accountcode],
Contract_Start As 'PlannedShift/Start',
Contract_End As 'PlannedShift/End',
Contract_BreakInMinutes As 'PlannedShift/BreakinMinutes',
Actual_Start As 'ActualShift/Start',
Actual_End As 'ActualShift/End',
Actual_BreakInMinutes As 'ActualShift/BreakinMinutes',
OverallCost As [OverallCost],
Agency_Worker_Name As 'AdditionalInformation/WorkerName',
Client As 'AdditionalInformation/Client',
Area As 'AdditionalInformation/Area',
-- ( select
-- 1 as Tag ,
-- 0 as Parent ,
-- (Select
-- Area
-- From #Temp M2
-- Where M1.Ref_Num = m2.Ref_Num)
-- As [Area!1!!CDATA]
-- for xml explicit
--) As 'AdditionalInformation/Area',
Assignment As 'AdditionalInformation/Assignment',
Commission As 'AdditionalInformation/Commission',
Total_Cost As 'AdditionalInformation/TotalCost',
Rate As 'AdditionalInformation/Rate'
From #Temp M1
for xml path('Shift'),Type)
for XML Path('Shifts'),Type
推荐答案
您似乎知道 CDAT A 有点过时了...如果您想阅读一些有关此内容的信息，请遵循此链接和此答案中的链接。
You seem to know that CDATA is rather outdated... If you want to read something about this you might follow this link and the links in this answer.
有时我们必须坚持下去……尤其是如果第三方工具做得不好的话。但是...
Sometimes we have to stick with it... Especially if poorly done third party tools demand for it. However...
包括 CDATA 节的唯一方法是使用 FOR XML显式，但这相当笨拙。
The only way to include CDATA sections is to use FOR XML EXPLICIT, but this is rather clumsy.
每当将包含 CDATA 部分的XML从字符串类型转换为本机XML时，您的CDATA都会丢失并且会正确地转义普通 text（）节点。
Whenever you convert an XML including a CDATA section from string type to native XML your CDATA will get lost and will be a properly escaped normal text() node.
尝试一下
DECLARE @tbl TABLE(XmlAsString NVARCHAR(MAX), NativeXml XML);
INSERT INTO @tbl
SELECT (
SELECT 1 AS Tag
,NULL AS Parent
,'test <&>' AS [SomeNode!1!!cdata]
FOR XML EXPLICIT
)
,(
SELECT 1 AS Tag
,NULL AS Parent
,'test <&>' AS [SomeNode!1!!cdata]
FOR XML EXPLICIT
);
SELECT * FROM @tbl
结果
<SomeNode><![CDATA[test <&>]]></SomeNode>
<SomeNode>test <&></SomeNode>
简而言之：保留CDATA节将迫使您保持字符串类型。可能会大大减少...
In short: Keeping CDATA sections forces you to remain in string type. Might be a big draw back...
如果我正确地看到了这一点，您想要获得上述所有内容，但
If I see this correctly, you want to get everything like above, but
<Area><![CDATA[ABCD]]></Area>
...而不是
<Area>ABCD</Area>
方法1（丑陋）：
按照上面的步骤创建XML，然后 read 读取< Area> 的内容，并使用 REPLACE 在字符串级别完全更改此节点。但是您绝不能将其转换回XML ...
Approach 1 (ugly):
Create the XML as you do it above, then read the content of <Area> and use a REPLACE on string level to change this node entirly. But you must not convert this ever back to XML...
这是带有 CDATA 部分的XML，不是绝对完整，但是您会看到以下原理：
This is your XML with the CDATA section, not absolutely complete, but you see the principles:
SELECT 1 AS Tag
,NULL AS Parent
,1220883 AS [Shifts!1!AgencyBackingReport]
,NULL AS [Shift!2!reference]
,NULL AS [Shift!2!startdate]
,NULL AS [Shift!2!accountcode]
,NULL AS [PlannedShift!3!Start!Element]
,NULL AS [PlannedShift!3!End!Element]
,NULL AS [PlannedShift!3!BreakingMinutes!Element]
,NULL AS [ActualShift!4!dummy!Element] --just a dummy
,NULL AS [OverallCost!5]
,NULL AS [AdditionalInformation!6!WorkerName!Element]
,NULL AS [AdditionalInformation!6!Area!CDATA]
,NULL AS [AdditionalInformation!6!Assignment!Element]
UNION ALL
SELECT 2
,1
,NULL
,83076641
,'2018-07-24'
,'ABCD'
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT 3
,2
,NULL
,NULL
,NULL
,NULL
,'09:00'
,'17:00'
,30
,NULL
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT 4 --just a dummy
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,'dummy'
,NULL
,NULL
,NULL
,NULL
UNION ALL
SELECT 5
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,211.59
,NULL
,NULL
,NULL
UNION ALL
SELECT 6
,2
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,'ABCD'
,'ABCD'
,'CPA00'
FOR XML EXPLICIT
结果
<Shifts AgencyBackingReport="1220883">
<Shift reference="83076641" startdate="2018-07-24" accountcode="ABCD">
<PlannedShift>
<Start>09:00</Start>
<End>17:00</End>
<BreakingMinutes>30</BreakingMinutes>
</PlannedShift>
<ActualShift>
<dummy>dummy</dummy>
</ActualShift>
<OverallCost>211.59</OverallCost>
<AdditionalInformation>
<WorkerName>ABCD</WorkerName>
<Area><![CDATA[ABCD]]></Area>
<Assignment>CPA00</Assignment>
</AdditionalInformation>
</Shift>
</Shifts>
这篇关于如何在SQL XML中使用CDATA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
