包装和删除XML周围的CDATA [英] Wrapping and removing CDATA around XML

问题描述

这是我的xml。我的目标是在导出时使用CDATA将数据包装在Value节点内，然后将其导入回删除了CDATA的Xml type列中。

This is my xml. My goal is to wrap the data inside the Value node with CDATA on an export and then import that back into an Xml type column with the CDATA removed.

<Custom>
     <Table>Shape</Table>
     <Column>CustomScreen</Column>
     <Value>Data</Value>
<Custom>

现在我要用表中的XML替换Value节点中的 Data，然后相信我正在使用CData，其中ShapeInfo是XML类型，而CustomPanel是[ShapeInfo] XML的第一个节点。

Right now I am replacing 'Data' inside the Value node with the XML from the table and then I believe I am putting CData around it, Where ShapeInfo is type XML and CustomPanel is the first node of [ShapeInfo] XML.

SET @OutputXML= replace(@OutputXML, 'Data', CAST((SELECT [ShapeInfo]      
                         FROM [Shape] WHERE [Shape_ID] = @ShapeID) as VARCHAR(MAX))

SET @OutputXML= replace(@OutputXML, '<CustomPanel', '<![CDATA[<CustomPanel')

但是结果看起来像这样，尽管我希望它只包含CDATA信息：

However the result looks something like this even though I expected it to only have CDATA around the information:

<Value>&lt;CustomPanel VisibilityIndicator=""&gt;&lText="No" Checked="False" Height="20" Width="50"/&gt;&lt;/Cell&gt;&lt;/Row&gt;&lt;/Table&gt;&lt;/CustomPanel&gt;</Value>

然后我正在执行一些动态SQL来更新该列

Then i am doing some dynamic sql to update that column

EXEC('UPDATE ['+ @tableName +  '] SET [' + @columnName + '] = ''' + @nodeValue + ''' WHERE Shape_ID = ''' + @ShapeID + '''')

I被告知我也许可以使用以下命令删除CDATA，但是我没有使用它。

I was told I might be able to use the following to remove CDATA but I didn't use it.

declare @x xml
set @x=N'<Value>&lt;CustomPanel....... all the current info ...=&quot;&quot;&gt;</Value>'

select @x.value('(/Value)[1]', 'nvarchar(max)')

select '<![CDATA[' + @x.value('(/Value)[1]', 'nvarchar(max)') + ']]'

之后再次检查该列，似乎它包含正确的信息。但是，我从未将其从VARCHAR改回XML或删除了CDATA符号，即使当我检查该列时它们似乎已经消失了。那我在这里想念什么？

After checking the column again it seems that it contains the correct information. However I never changed it back to XML from VARCHAR or removed the CDATA symbols even though they seem to be gone when I checked the column. So what am I missing here? Is this a correct way to do it?

推荐答案

如果您需要对生成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.

这篇关于包装和删除XML周围的CDATA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！