T-SQL XML - how to divide CDATA in xml node

Question

I am using MS SQL Server 2012 and have the following XML structure:

<value name="parameter">
    <![CDATA[__na__]]>
    <![CDATA[1]]>
    <![CDATA[2]]>
    <![CDATA[3]]>
    <![CDATA[12]]>
</value>

and it is saved in the database as string in NVARCHAR(MAX) data type field.

When I cast this value as follows:

CAST(ColumnValue AS XML)

I get the following XML structure:

<value name="parameter">__na__12312</value>

Is there a way to divide the CDATA with commas for exmaple and get the following structure:

<value name="parameter">__na__,1,2,3,12</value>

In my real case the string length can vary and I am using only part of the whole XML, so replacing each ']]>' with ']]>,' does not sound as good solution. Is there an other way to do this?

related [Sql Server 2005 Xml data type has CDATA removed on INSERT](http://stackoverflow.com/questions/735646) and similar - looks like as soon as the data is in the `xml` type, `CDATA` information is lost. — AakashM, Apr 18 '13 at 09:25
By definition: CDATA is used to encase text data that should NOT be parsed by a XML parser, so additional controls are needed to circumvent this. For MS-SQL 2012, See if this answer supports your efforts: http://stackoverflow.com/questions/1429351/sql-server-xml-output-with-cdata which provides information on controlling the FOR XML EXPLICIT. A bit off topic, but this is a good article on the difference between CAST() and CONVERT() for XML data, https://www.simple-talk.com/sql/database-administration/converting-string-data-to-xml-and-xml-to-string-data/ — Newbyman, Jun 07 '13 at 00:35

score 1 · Accepted Answer · answered Oct 23 '13 at 14:43

If you dont mind to have the first character with a precedding comma you could try this:

/*Loading test data*/
declare @xml varchar(8000)
set @xml = '
<value name="parameter">
    <![CDATA[__na__]]>
    <![CDATA[1]]>
    <![CDATA[2]]>
    <![CDATA[3]]>
    <![CDATA[12]]>
</value>'

/*the replace*/
set @xml= REPLACE(@xml,'<![CDATA[','<![CDATA[,' )
select CAST(@xml AS XML)

It will result in:

<value name="parameter">,__na__    ,1    ,2    ,3    ,12    </value>

This replace ensures you only change the CDATA's and not other xml parameters or values and when you recover the value "parameter" you only need to ignore the first character

T-SQL XML - how to divide CDATA in xml node

1 Answers1