SQL Server

XML SearchForTag Method

See more XML Examples

Demonstrates the SearchForTag and SearchForTag2 methods.

The input XML, available at http://www.chilkatsoft.com/data/fruitSearch.xml, is this:

<root>
    <searchRoot>
        <fruit color="red">apple</fruit>
        <fruit color="green">pear</fruit>
        <veg color="orange">carrot</veg>
        <meat animal="cow">beef</meat>
        <xyz>
            <fruit color="blue">blueberry</fruit>
            <veg color="green">broccoli</veg>
        </xyz>
        <fruit color="purple">grape</fruit>
        <cheese color="yellow">cheddar</cheese>
    </searchRoot>
    <fruit color="red">strawberry</fruit>
    <fruit color="orange">peach</fruit>
</root>

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

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

    DECLARE @xSearchRoot int

    DECLARE @xBeginAfter int

    DECLARE @xFound int

    -- The sample input XML is available at http://www.chilkatsoft.com/data/fruitSearch.xml
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'qa_data/xml/fruitSearch.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Search the sub-tree rooted at "searchRoot"
    EXEC sp_OAMethod @xml, 'FindChild', @xSearchRoot OUT, 'searchRoot'

    -- Search for all nodes having the tag "fruit"
    EXEC sp_OAMethod @xSearchRoot, 'GetSelf', @xBeginAfter OUT
    EXEC sp_OAMethod @xSearchRoot, 'SearchForTag', @xFound OUT, @xSearchRoot, 'fruit'
    EXEC sp_OAGetProperty @xSearchRoot, 'LastMethodSuccess', @iTmp0 OUT
    WHILE (@iTmp0 = 1)
      BEGIN

        EXEC sp_OAGetProperty @xFound, 'Content', @sTmp0 OUT
        PRINT @sTmp0

        EXEC @hr = sp_OADestroy @xBeginAfter

        SELECT @xBeginAfter = @xFound
        EXEC sp_OAMethod @xSearchRoot, 'SearchForTag', @xFound OUT, @xBeginAfter, 'fruit'
      END

    EXEC @hr = sp_OADestroy @xBeginAfter


    PRINT '--------------------------'

    -- ---------------------------------------------------------------------------------
    -- Now do the same, but instead use SearchForTag2
    -- which updates the internal reference of the caller instead
    -- of returning the found node.
    DECLARE @xSearch int

    EXEC sp_OAMethod @xSearchRoot, 'GetSelf', @xBeginAfter OUT
    EXEC sp_OAMethod @xSearchRoot, 'GetSelf', @xSearch OUT

    EXEC sp_OAMethod @xSearch, 'SearchForTag2', @success OUT, @xBeginAfter, 'fruit'
    WHILE @success = 1
      BEGIN

        EXEC sp_OAGetProperty @xSearch, 'Content', @sTmp0 OUT
        PRINT @sTmp0

        -- Copy the internal references so that the next search
        -- begins after the found node.
        EXEC sp_OAMethod @xBeginAfter, 'CopyRef', NULL, @xSearch
        EXEC sp_OAMethod @xSearch, 'CopyRef', NULL, @xSearchRoot

        EXEC sp_OAMethod @xSearch, 'SearchForTag2', @success OUT, @xBeginAfter, 'fruit'
      END

    EXEC @hr = sp_OADestroy @xSearch

    EXEC @hr = sp_OADestroy @xBeginAfter

    EXEC @hr = sp_OADestroy @xSearchRoot


    EXEC @hr = sp_OADestroy @xml


END
GO