(SQL Server) Create XML with Multiple Same-Tag Children

Demonstrates how to create XML where siblings have the identical tags.

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)
    -- This example creates the following XML:

    -- <soap:Envelope>
    --     <soap:Body>
    --         <S:AlternatePartyIds>
    --             <S:Id>123456789</S:Id>
    --             <S:AssigningPartyId>NationalId</S:AssigningPartyId>
    --         </S:AlternatePartyIds>
    --         <S:AlternatePartyIds>
    --             <S:Id>987654</S:Id>
    --             <S:AssigningPartyId>DriversLicense</S:AssigningPartyId>
    --             <S:IssuingState>SD</S:IssuingState>
    --         </S:AlternatePartyIds>
    --     </soap:Body>
    -- </soap:Envelope>

    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

    EXEC sp_OASetProperty @xml, 'Tag', 'soap:Envelope'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soap:Body|S:AlternatePartyIds|S:Id', '123456789'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soap:Body|S:AlternatePartyIds|S:AssigningPartyId', 'NationalId'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soap:Body|S:AlternatePartyIds[1]|S:Id', '987654'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soap:Body|S:AlternatePartyIds[1]|S:AssigningPartyId', 'DriversLicense'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soap:Body|S:AlternatePartyIds[1]|S:IssuingState', 'SD'

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

    EXEC @hr = sp_OADestroy @xml


END
GO