(SQL Server) XML Document References

This example demonstrates the fact that the entire XML document remains in memory if at least one node is referenced. (It does not need to be the root node.)

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
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, '<a><b>BBB</b><c>CCC</c></a>'

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

    -- We have the following XML stored in memory:

    -- 	<?xml version="1.0" encoding="utf-8"?>
    -- 	<a>
    -- 	    <b>BBB</b>
    -- 	    <c>CCC</c>
    -- 	</a>

    -- Get a reference to the 1st child.
    DECLARE @xB int
    EXEC sp_OAMethod @xml, 'GetChild', @xB OUT, 0

    -- Re-load "xml" with an entirely new document:
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, '<z><x>XXX</x></z>'

    -- What happens to xB?
    -- The answer is nothing.  xB still points ot the "b" node in the original document, and the entire
    -- original document remains in memory.  We now have two XML documents in memory.

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

    -- output is:

    --     <b>BBB</b>

    -- Make xB reference the root node of its document:
    EXEC sp_OAMethod @xB, 'GetRoot2', NULL
    EXEC sp_OAMethod @xB, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    -- You can see here that the entire original XML document is still available
    -- because at least one node in the XML is referenced by a variable.
    -- The output is:

    --     <?xml version="1.0" encoding="utf-8"?>
    --     <a>
    --         <b>BBB</b>
    --         <c>CCC</c>
    --     </a>

    -- We can also see that "xml" contains an entirely new XML document:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    --     <?xml version="1.0" encoding="utf-8"?>
    --     <z>
    --         <x>XXX</x>
    --     </z>
    -- 

    EXEC @hr = sp_OADestroy @xB


    EXEC @hr = sp_OADestroy @xml


END
GO