(SQL Server) XML SearchForAttribute Method

Demonstrates the SearchForAttribute method.

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></root>
</root>

Download Chilkat XML ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 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

    DECLARE @xSearchRoot int

    DECLARE @xBeginAfter int

    DECLARE @xFound int

    DECLARE @success int

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

        PRINT @sTmp0
        RETURN
      END

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

    --  Search for all "fruit" nodes having a color attribute
    --  where the name of the color ends in "e"
    SELECT @xBeginAfter = NULL
    EXEC sp_OAMethod @xSearchRoot, 'SearchForAttribute', @xFound OUT, @xBeginAfter, 'fruit', 'color', '*e'
    WHILE Not (@xFound Is NULL )
      BEGIN

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


        EXEC sp_OAMethod @xFound, 'GetAttrValue', @sTmp1 OUT, 'color'
        PRINT @sTmp0 + ': ' + @sTmp1

        SELECT @xBeginAfter = @xFound
        EXEC sp_OAMethod @xSearchRoot, 'SearchForAttribute', @xFound OUT, @xBeginAfter, 'fruit', 'color', '*e'
      END

    --  The correct output is:
    --  grape: purple
    --  blueberry: blue


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

    --  ---------------------------------------------------------------------------------
    --  Now do the same, but instead use SearchForAttribute2
    --  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, 'SearchForAttribute2', @success OUT, @xBeginAfter, 'fruit', 'color', '*e'
    WHILE @success = 1
      BEGIN

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


        EXEC sp_OAMethod @xSearch, 'GetAttrValue', @sTmp1 OUT, 'color'
        PRINT @sTmp0 + ': ' + @sTmp1

        --  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, 'SearchForAttribute2', @success OUT, @xBeginAfter, 'fruit', 'color', '*e'
      END


END
GO