(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>

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

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

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

    -- 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', @success OUT
    EXEC sp_OAMethod @xml, 'UnzipContent', @success OUT
    EXEC sp_OAMethod @xml, 'GetRoot2', NULL
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xml


END
GO