(SQL Server) Compress XML Content

Demonstrates how to compress the content of an XML node. Note: This does not compress the node's children, only the text content.

The input XML, available at http://www.chilkatsoft.com/data/compress1.xml, is this:

<root>
    <fox>This is content that will be compressed.
		0The quick brown fox jumps over the lazy dog
		1The quick brown fox jumps over the lazy dog
		2The quick brown fox jumps over the lazy dog
		3The quick brown fox jumps over the lazy dog
		4The quick brown fox jumps over the lazy dog
		5The quick brown fox jumps over the lazy dog
		6The quick brown fox jumps over the lazy dog
		7The quick brown fox jumps over the lazy dog
		8The quick brown fox jumps over the lazy dog
		9The quick brown fox jumps over the lazy dog
        <child1>
            <grandchild>This is not compressed.</grandchild>
        </child1>
    </fox>
</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 @success int

    --  The sample input XML is available at http://www.chilkatsoft.com/data/compress1.xml
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'compress1.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    --  Navigate to the "fox" node, which is the 1st child:
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL

    --  Zip compress the content:
    EXEC sp_OAMethod @xml, 'ZipContent', NULL

    --  Navigate back to the root:
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL

    --  Examine the new XML document:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    --  This is the XML w/ the compressed content in Base64 encoded format:
    --  
<root>
    <fox><![CDATA[lcvbEYIwFIThZ5ixh63A8QZqHzTA5UiiIQeTIGr1xhJ2Zp/++bYxNiKvV5/EJyTTJqzWOXSS4zQH
iVGG7aYsil1jBM/F9g90QVePm75xX6Y5Ql8S8lfg2u8Hg45/vyf9gfRH0p9IX5G+Jv2Z9BfSXwn/
Aw==
]]>
        <child1>
            <grandchild>This is not compressed.</grandchild>
        </child1>
    </fox>
</root>


    --  Now uncompress and show that the original content was restored:
    EXEC sp_OAMethod @xml, 'FirstChild2', NULL
    EXEC sp_OAMethod @xml, 'UnzipContent', NULL
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0


END
GO