SQL Server

Insert after Node with Tag

See more XML Examples

Demonstrates how to insert a new node as a sibling directly after a specified node having a given tag.

Note: This example requires Chilkat v9.5.0.76 or greater. The TagIndex method was introduced in v9.5.0.76

SQL Server

-- 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)
    -- First, let's create the following XML:

    --   <FatturaElettronicaHeader>
    --     <DatiTrasmissione>
    --       <IdTrasmittente>
    --         <IdPaese>IT</IdPaese>
    --         <IdCodice>12345678</IdCodice>
    --       </IdTrasmittente>
    --     </DatiTrasmissione>
    --   </FatturaElettronicaHeader>

    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', 'FatturaElettronicaHeader'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DatiTrasmissione|IdTrasmittente|IdPaese', 'IT'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'DatiTrasmissione|IdTrasmittente|IdCodice', '12345678'

    -- Now insert a new node to get this XML:

    --   <FatturaElettronicaHeader>
    --     <DatiTrasmissione>
    --       <IdTrasmittente>
    --         <IdPaese>IT</IdPaese>
    --         <NewTag>Content</NewTag>
    --         <IdCodice>12345678</IdCodice>
    --       </IdTrasmittente>
    --     </DatiTrasmissione>
    --   </FatturaElettronicaHeader>

    DECLARE @xT int
    EXEC sp_OAMethod @xml, 'FindChild', @xT OUT, 'DatiTrasmissione|IdTrasmittente'
    DECLARE @index int
    EXEC sp_OAMethod @xT, 'TagIndex', @index OUT, 'IdPaese'
    IF @index < 0
      BEGIN

        PRINT 'nothing found at the given tag.'
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    DECLARE @x1 int
    EXEC sp_OAMethod @xT, 'NewChildAfter', @x1 OUT, @index, 'NewTag', 'Content'
    EXEC @hr = sp_OADestroy @x1

    EXEC @hr = sp_OADestroy @xT

    -- Show the resulting XML.
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xml


END
GO