Search code examples
sqlsql-serverxmlcdata

Wrapping and removing CDATA around XML

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>

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

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>

Then i am doing some dynamic sql to update that column

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

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)') + ']]'

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?

Solution

  • 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

    It generates:

    <Custom>
  <Table>Shape</Table>
  <Column>CustomScreen</Column>
  <Value><![CDATA[Data]]></Value>
</Custom>

    If you need reverse, replace source XML and use ELEMENT instead of CDATA.