SQL Server

Using CDATA in XML

See more XML Examples

Demonstrates how to force the content of a node to be encapsulated in CDATA.

The output of the following program is an XML document that looks like this:

<root>
    <year>2009</year>
    <junk1>abc .. &lt; &amp; &gt; 123</junk1>
    <junk2><![CDATA[abc .. < & > 123]]></junk2>
</root>

SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @child1 int

    DECLARE @child2 int

    DECLARE @child3 int

    EXEC sp_OASetProperty @xml, 'Tag', 'root'

    EXEC sp_OAMethod @xml, 'NewChild', @child1 OUT, 'year', '2009'

    EXEC sp_OAMethod @xml, 'NewChild', @child2 OUT, 'junk1', 'abc .. < & > 123'

    EXEC sp_OAMethod @xml, 'NewChild', @child3 OUT, 'junk2', 'abc .. < & > 123'
    EXEC sp_OASetProperty @child3, 'Cdata', 1

    EXEC @hr = sp_OADestroy @child1

    EXEC @hr = sp_OADestroy @child2

    EXEC @hr = sp_OADestroy @child3

    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xml


END
GO