更新时间:2023-12-05 22:37:10
如果您需要对生成XML的完全控制,则可以使用 FOR XML EXPLICIT
:
If you need full control over generating XML, you can use FOR XML EXPLICIT
:
DECLARE @xml xml = '<Custom>
<Table>Shape</Table>
<Column>CustomScreen</Column>
<Value>Data</Value>
</Custom>';
WITH rawValues AS
(
SELECT
n.value('Table[1]', 'nvarchar(20)') [Table],
n.value('Column[1]', 'nvarchar(20)') [Column],
n.value('Value[1]', 'nvarchar(20)') [Value]
FROM @xml.nodes('Custom') X(n)
)
SELECT 1 AS Tag,
NULL AS Parent,
[Table] AS [Custom!1!Table!ELEMENT],
[Column] AS [Custom!1!Column!ELEMENT],
[Value] AS [Custom!1!Value!CDATA]
FROM rawValues
FOR XML EXPLICIT
它生成:
<Custom>
<Table>Shape</Table>
<Column>CustomScreen</Column>
<Value><![CDATA[Data]]></Value>
</Custom>
如果需要反向,请替换源XML并使用 ELEMENT
而不是 CDATA
。
If you need reverse, replace source XML and use ELEMENT
instead of CDATA
.