Generating XML CData Element from SELECT Statement

MS SQL Server has data type of XML since SQL Server 2005. The XML data type is a subset of NVARCHAR or VARCHAR, so string data can be easily converted to XML value by running the following query/function.

DECLARE @data AS NVARCHAR(MAX)
SET @data = '<product>
               <id>1</id>
               <name>My Product</name>
               <description>My Description</description>
               <price>19.99</price>
             </product>'

DECLARE @xml AS XML
SET @xml = CAST(@data AS XML)

SELECT @data, @xml
`</pre>

However, if the `&lt;description&gt;` element contains HTML value like, `&lt;strong&gt;My&lt;/strong&gt; Description`, those HTML tags are encoded. As a result, the return value will be:

<pre>`&lt;product&gt;
  &lt;id&gt;1&lt;/id&gt;
  &lt;name&gt;My Product&lt;/name&gt;
  &lt;description&gt;&amp;lt;strong&amp;gt;My&amp;lt;/strong&amp;gt; Description&lt;/description&gt;
  &lt;price&gt;19.99&lt;/price&gt;
&lt;/product&gt;
`</pre>

In order to avoid this situation, `&lt;![CDATA[...]]&gt;` should be used. How can we achieve it? Let's assume that there is a record on the `@products` table.

<pre>`DECLARE @products TABLE (
    id          INT,
    name        NVARCHAR(MAX),
    description NVARCHAR(MAX),
    price       DECIMAL(18, 2)
)

INSERT INTO @products (
    [id], [name],        [description],                       [price]
) VALUES (
    1,    'My Product',  '&lt;strong&gt;My&lt;/strong&gt; Description',   19.99
)

SELECT id, name, description, price FROM @products WHERE id = 1
`</pre>

It returns the record like:

<table>
<thead>
<tr>
  <th>id</th>
  <th>name</th>
  <th>description</th>
  <th>price</th>
</tr>
</thead>
<tbody>
<tr>
  <td>1</td>
  <td>My Product</td>
  <td>&lt;strong&gt;My&lt;/strong&gt; Description</td>
  <td>19.99</td>
</tr>
</tbody>
</table>

Now, let's convert this record to XML, especially with `CDATA` tag.

<pre>`DECLARE @result AS NVARCHAR(MAX)
SET @result = (
    SELECT * FROM (
        SELECT
            1              AS Tag,
            0              AS Parent,
            [id]           AS [product!1!id!Element],
            [name]         AS [product!1!name!Element],
            NULL           AS [description!2!!CDATA],
            [price]        AS [product!1!price!Element]
          FROM
            @products
         WHERE [id] = 1
        UNION ALL
        SELECT
            2              AS Tag,
            1              AS Parent,
            NULL,
            NULL,
            [description],
            NULL
          FROM
            @products
         WHERE [id] = 1
    ) AS q
    FOR XML EXPLICIT
)

SELECT @result AS [String], CAST(@result AS XML) AS [XML]
`</pre>

And you will find the `String` field keeps `CDATA` value:

<pre>`&lt;product&gt;&lt;id&gt;1&lt;/id&gt;&lt;name&gt;My Product&lt;/name&gt;&lt;price&gt;19.9900&lt;/price&gt;&lt;description&gt;&lt;![CDATA[&lt;strong&gt;My&lt;/strong&gt; Description]]&gt;&lt;/description&gt;&lt;/product&gt;
`</pre>

While, the `XML` field converts the value HTML encoded:

<pre>`&lt;product&gt;
  &lt;id&gt;1&lt;/id&gt;
  &lt;name&gt;My Product&lt;/name&gt;
  &lt;price&gt;19.9900&lt;/price&gt;
  &lt;description&gt;&amp;lt;strong&amp;gt;My&amp;lt;/strong&amp;gt; Description&lt;/description&gt;
&lt;/product&gt;