XML Cdata 格式和变量输入 TSQL

Question

DECLARE @TransactionId NVARCHAR(100)
DECLARE @TransactionDateTime DATETIME
--Setting Variable
SET @TransactionId= (SELECT CONVERT(VARCHAR, CURRENT_TRANSACTION_ID()))
SET @TransactionDateTime= GETDATE()
--Start the XML Selction
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'Tag!1!',
NULL AS 'TransactionType!2!CollectSampling!cdata',
NULL AS 'TransactionID!2!TransactionId!cdata',
NULL AS 'TransactionDateTime!2!TransactionDateTime!cdata',
NULL AS 'ContainerName!2!Name!cdata',
NULL AS 'Make!2!Make!cdata',
NULL AS 'Model!2!Model!cdata',
NULL AS 'Price!2!Price!cdata',
NULL AS  'Type!2!Type!cdata'
Union  ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
'CollectSample',
@TransactionId,
@TransactionDateTime,
[Name] ,
[Make] ,
[Model] ,
[Price]  ,
[Type]
from dbo.RepCar
FOR XML EXPLICIT,  ROOT('Message')

以下是我想要的输出，但不断收到 XML 格式错误。也只是消息是父级 希望输出如下。我正在使用变量来填充几个标签，而 CollectSample 是静态字段。

<Message>
<TransactionType><![CDATA[CollectSamplingData]]></TransactionType>
<TransactionID><![CDATA[0CA4E46F-5143-498C-B1AC-F990FF70462E]]></TransactionID>
<TransactionDate><![CDATA[2020-01-10T14:20:30-05:00]]></TransactionDate>
<CName><![CDATA[Name]]></CName>
<MakeCar><![CDATA[Make]]></MakeCar>
<MakeModel><![CDATA[Model]]></MakeModel>
<DataValue><![CDATA[Price]]></DataValue>
<MakeType><![CDATA[Type]]></MakeType>
</Message>

Answer 1

declare @RepCar table
(
[Name] varchar(10),
[Make] varchar(10),
[Model] varchar(10),
[Price]  money,
[Type] varchar(10)
);
insert into @RepCar
(
Name, Make, Model, Price, Type
)
values
('Car1', 'Make1', 'Model1', 100, 'Type1'),
('Car2', 'Make2', 'Model2', 200, 'Type2'),
('Car3', 'Make3', 'Model3', 300, 'Type3');

DECLARE @TransactionId NVARCHAR(100)
DECLARE @TransactionDateTime DATETIME
--Setting Variable
SET @TransactionId= (SELECT CONVERT(VARCHAR, CURRENT_TRANSACTION_ID()))
SET @TransactionDateTime= GETDATE()
--Start the XML Selction

/*
ElementName!TagNumber!AttributeName!Directive 
AttributeName
Provides the name of the attribute to construct in the specified ElementName. This is the behavior if Directive is not specified.
!!!! If Directive is specified and it is xml, cdata, or element, this value is used to construct an element child of ElementName, and the column value is added to it.!!!!
*/

select 1 AS Tag,
0 AS Parent,
'CollectSamplingData' as 'Message!1!TransactionType!cdata',
@TransactionId as 'Message!1!TransactionID!cdata',
@TransactionDateTime  as 'Message!1!TransactionDate!cdata',
[Name]  as 'Message!1!CName!cdata',
[Make]  as 'Message!1!MakeCar!cdata',
[Model]  as 'Message!1!MakeModel!cdata',
[Price]   as 'Message!1!DataValue!cdata',
[Type]  as 'Message!1!MakeType!cdata'
from @RepCar --dbo.RepCar
FOR XML EXPLICIT, ROOT('Messages');