(SQL Server) Using CDATA in XML

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>

Download Chilkat XML ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    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 sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0
END
GO