Navigate to Content in XML

Demonstrates how to retrieve "Bears" from the following XML:

<?xml version="1.0" encoding="utf-8" ?>

<sports>
    <baseball>
        <mlb>
            <chicago>Cubs</chicago>
            <new_york>Yankees</new_york>
        </mlb>
    </baseball>
    <football>
        <chicago>Bears</chicago>
        <new_york>Jets</new_york>
    </football>
    <basketball>
        <chicago>Bulls</chicago>
        <new_york>Knicks</new_york>
    </basketball>
</sports>

Download Chilkat XML ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)

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

    --  Assume the XML is contained in the file: sports.xml
    DECLARE @success int

    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'sports.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT

        PRINT @sTmp0
        RETURN
      END

    DECLARE @xml2 int

    DECLARE @xml3 int

    EXEC sp_OAMethod @xml, 'GetChildWithTag', @xml2 OUT, 'football'

    EXEC sp_OAMethod @xml2, 'GetChildWithTag', @xml3 OUT, 'chicago'

    EXEC sp_OAGetProperty @xml3, 'Content', @sTmp0 OUT

    PRINT @sTmp0


END
GO