Sql-docs: “Inserting data using a CDATA section” has incorrect formatting

Created on 6 May 2020 · 4Comments · Source: MicrosoftDocs/sql-docs

The documentation tries to say that < is inserted as < but it instead says that < is inserted as <.

The documentation’s example of the output XML also is invalid. It says the result is:

<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features> <notxml> as text </notxml> or cdata </Features>
</ProductDescription>
</Root>

when the actual result is

<Root>
  <ProductDescription ProductID="1" ProductName="Road Bike">
    <Features> &lt;notxml&gt; as text &lt;/notxml&gt; or cdata </Features>
  </ProductDescription>
</Root>

Note that to get this result, the query I had to use was the following because I couldn’t figure out how to get the XPath to let me construct <![CDATA[…]]>. I am using SQL Server 11. Is support for direct construction of CDATA in expressions a relatively new feature?

DECLARE @myDoc XML = '<Root>
    <ProductDescription ProductID="1" ProductName="Road Bike">
        <Features/>
    </ProductDescription>
</Root>';
SELECT @myDoc;
DECLARE @x XML = N'<![CDATA[ <notxml> as text </notxml> or cdata ]]>';
SET @myDoc.modify('
insert sql:variable(''@x'')
into  (/Root/ProductDescription/Features)[1]
');
SELECT @myDoc;