(SQL Server) Handling Namespaces in XML Tags

See more XML Examples

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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- First create some XML using namespace prefixed tags.

    -- Create this XML. 
    -- Use this online tool to generate the code from sample XML: 
    -- Generate Code to Create XML

    -- <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
    --    xmlns:scm="http://www.springcm.com/atlas/webservices/v201308/scm/">
    --    <soapenv:Header/>
    --    <soapenv:Body>
    --       <scm:AccountGetCurrent>
    --          <scm:token>123</scm:token>
    --       </scm:AccountGetCurrent>
    --    </soapenv:Body>
    -- </soapenv: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', 'soapenv:Envelope'
    DECLARE @success int
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:soapenv', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:scm', 'http://www.springcm.com/atlas/webservices/v201308/scm/'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Header', ''
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soapenv:Body|scm:AccountGetCurrent|scm:token', '123'

    -- Go to the "Body" tag, regardless of namespace prefix.
    DECLARE @xmlBody int
    EXEC sp_OAMethod @xml, 'FindChild', @xmlBody OUT, '*:Body'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Body child not found.'
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Get the full tag w/ namespace prefix:
    -- Output is "soapenv:Body"
    EXEC sp_OAGetProperty @xmlBody, 'Tag', @sTmp0 OUT
    PRINT @sTmp0

    -- Get the namespace prefix:
    -- Output is "soapenv"
    EXEC sp_OAGetProperty @xmlBody, 'TagNsPrefix', @sTmp0 OUT
    PRINT @sTmp0

    -- Get the unprefixed tag:
    -- Output is "Body"
    EXEC sp_OAGetProperty @xmlBody, 'TagUnprefixed', @sTmp0 OUT
    PRINT @sTmp0

    -- Check to see if the unprefixed tag equals "Body"
    DECLARE @isBodyTag int
    EXEC sp_OAMethod @xmlBody, 'TagUnpEquals', @isBodyTag OUT, 'Body'

    PRINT 'isBodyTag = ' + @isBodyTag

    -- Check to see if the namespace prefix equals "soapenv";
    DECLARE @isSoapenv int
    EXEC sp_OAMethod @xmlBody, 'TagNsEquals', @isSoapenv OUT, 'soapenv'

    PRINT 'isSoapenv = ' + @isSoapenv

    -- Change just the namespace prefix of the tag
    EXEC sp_OASetProperty @xmlBody, 'TagNsPrefix', 'se'
    -- The tag has changed from "soapenv:Body" to "se:Body"
    EXEC sp_OAGetProperty @xmlBody, 'Tag', @sTmp0 OUT
    PRINT @sTmp0

    -- Change the unprefixed part of the tag.
    EXEC sp_OASetProperty @xmlBody, 'TagUnprefixed', 'Bodie'
    -- The tag has changed from "se:Body" to "se:Bodie"
    EXEC sp_OAGetProperty @xmlBody, 'Tag', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xmlBody


    EXEC @hr = sp_OADestroy @xml


END
GO