(SQL Server) Insert XML into existing XML by calling InsertChildTreeAfter

Demonstrates how to insert an XML tree or subtree into another XML document using the InsertChildTreeAfter method.

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
    DECLARE @sTmp0 nvarchar(4000)
    -- Build the 1st XML document:
    -- <x1>
    --     <a1>1</a1>
    --     <a2>2</a2>
    --     <a3>3</a3>
    -- </x1>

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

    EXEC sp_OASetProperty @xml, 'Tag', 'x1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'a1', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'a2', '2'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'a3', '3'

    -- Build a 2nd XML document:

    -- <x2>
    --   <b1>11</b1>
    --   <b2>22</b2>
    --   <b3>33</b3>
    -- </x2>

    DECLARE @xml2 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml2 OUT

    EXEC sp_OASetProperty @xml2, 'Tag', 'x2'
    EXEC sp_OAMethod @xml2, 'UpdateChildContent', NULL, 'b1', '11'
    EXEC sp_OAMethod @xml2, 'UpdateChildContent', NULL, 'b2', '22'
    EXEC sp_OAMethod @xml2, 'UpdateChildContent', NULL, 'b3', '33'

    -- We want to insert xml2 into xml to get this:

    -- <x1>
    --     <a1>1</a1>
    --     <x2>
    --         <b1>11</b1>
    --         <b2>22</b2>
    --         <b3>33</b3>
    --     </x2>
    --     <a2>2</a2>
    --     <a3>3</a3>
    -- </x1>

    -- Insert xml2 after "a1"

    DECLARE @index int
    EXEC sp_OAMethod @xml, 'TagIndex', @index OUT, 'a1'
    IF @index < 0
      BEGIN

        PRINT 'a1 not found.'
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @xml2
        RETURN
      END

    EXEC sp_OAMethod @xml, 'InsertChildTreeAfter', NULL, @index, @xml2

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

    -- The result is:

    -- <x1>
    --     <a1>1</a1>
    --     <x2>
    --         <b1>11</b1>
    --         <b2>22</b2>
    --         <b3>33</b3>
    --     </x2>
    --     <a2>2</a2>
    --     <a3>3</a3>
    -- </x1>

    -- Note: xml2 now points to the subtree within xml.
    -- For example, update "b3" and then see how it's updated within the merged document:
    EXEC sp_OAMethod @xml2, 'UpdateChildContent', NULL, 'b3', '33333333'

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

    -- The result:

    -- <x1>
    --     <a1>1</a1>
    --     <x2>
    --         <b1>11</b1>
    --         <b2>22</b2>
    --         <b3>33333333</b3>
    --     </x2>
    --     <a2>2</a2>
    --     <a3>3</a3>
    -- </x1>

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @xml2


END
GO