Encrypt XML Subtree

Encrypt an entire subtree within an XML document

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

    --  First, build a sample XML document:

    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xml, 'Tag', 'stocks'

    DECLARE @xmlM int

    EXEC sp_OAMethod @xml, 'NewChild', @xmlM OUT, 'Microsoft', ''
    EXEC sp_OAMethod @xmlM, 'NewChild2', NULL, 'symbol', 'MSFT'
    EXEC sp_OAMethod @xmlM, 'NewChild2', NULL, 'recentPrice', '34.50'

    DECLARE @xmlG int

    EXEC sp_OAMethod @xml, 'NewChild', @xmlG OUT, 'Google', ''
    EXEC sp_OAMethod @xmlG, 'NewChild2', NULL, 'symbol', 'GOOG'
    EXEC sp_OAMethod @xmlG, 'NewChild2', NULL, 'recentPrice', '679.00'

    --  Display the unencrypted XML:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    --  This is the XML displayed:
    --  <stocks>
    --      <Microsoft>
    --          <symbol>MSFT</symbol>
    --          <recentPrice>34.50</recentPrice>
    --      </Microsoft>
    --      <Google>
    --          <symbol>GOOG</symbol>
    --          <recentPrice>679.00</recentPrice>
    --      </Google>
    --  </stocks>

    --  The goal is to encrypt the "Microsoft" sub-tree.
    --  The EncryptContent method encryptes the content
    --  within a single XML node (not an entire sub-tree).
    --  Therefore, to encrypt a sub-tree,  you must first
    --  compress it into a single node, and then encrypt it.
    EXEC sp_OAMethod @xmlM, 'ZipTree', NULL

    --  Now encrypt the content using 128-bit AES encryption:
    EXEC sp_OAMethod @xmlM, 'EncryptContent', NULL, 'secretPassword'

    --  Display the XML with the encrypted sub-tree:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    --  This is displayed:
    --  <stocks>
    --      <Microsoft><![CDATA
    --  [JkiMcoK3Mi198rT30KvicDEzu5WuJgMH+8KS0UZbcRE
    --  2CtdXplphV0iCdPMYnS01O6Ly6S4QCQSReMCshG/V3
    --  8btJIZv/VLG9JZRsQk0bBafMhx7B2fQfm8YENke/JIM
    --  ]]>    </Microsoft>
    --      <Google>
    --          <symbol>GOOG</symbol>
    --          <recentPrice>679.00</recentPrice>
    --      </Google>
    --  </stocks>

    --  Now decrypt and unzip:
    EXEC sp_OAMethod @xmlM, 'DecryptContent', NULL, 'secretPassword'
    DECLARE @success int

    EXEC sp_OAMethod @xmlM, 'UnzipTree', @success OUT
    IF @success <> 1
      BEGIN

        PRINT 'Failed to unzip tree'
      END

    --  Display the restored document:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0


END
GO