(SQL Server) Traverse the XML Tree

Demonstrates how to traverse all nodes in an XML tree.

Note: This example requires Chilkat v9.5.0.77 or greater.

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)
    -- Use the NextInTraversal2 method to traverse all nodes in the XML tree rooted at the caller.

    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

    DECLARE @success int
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'qa_data/xml/twoPigs.xml'

    -- This is the XML traversed in this example:

    -- <pig-rescue>
    --   <herd name="Cathy">
    -- 	<species name="pot belly pig">
    -- 	    <animal>
    -- 	        <name>Molly II</name>
    -- 	        <birth>February, 1998</birth>
    -- 			<in-date>January, 2000</in-date>
    -- 			<from>Middle Ave.</from>
    -- 			<gender spay-neuter="yes">F</gender>
    -- 			<info>Molly came from a place where an old, ill man had too many animals.
    -- 	              	The animals were seldom fed and rats would eat the newborn piglets.
    -- 	             	 When Molly was found at one week old, she was the only piglet of 
    -- 	              	her siblings who did not get stepped on by a cow. She is a sweet, 
    -- 	              	friendly pig who likes to hang out on Cathy's porch on the lounge 
    -- 	              	pad.
    -- 			</info>
    -- 		</animal>
    -- 	    <animal>
    -- 			<name>Nigel</name>
    -- 			<birth>June, 1991</birth>
    -- 			<in-date>August, 1991</in-date>
    -- 			<from>Breeder</from>
    -- 			<gender spay-neuter="no">M</gender>
    -- 			<info>
    -- 			 	Nigel was the first pig and the reason for the rescue in the
    -- 			 	first place. He is 13 now and doing well, but takes a number 
    -- 				of medications, as you can see below.
    -- 			</info>
    -- 		</animal>		
    -- 	</species>
    --   </herd>
    -- </pig-rescue>

    -- The sbState is an object used to keep track of the current state of the traversal.
    DECLARE @sbState int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbState OUT

    EXEC sp_OAMethod @xml, 'NextInTraversal2', @iTmp0 OUT, @sbState
    WHILE @iTmp0 <> 0
      BEGIN

        EXEC sp_OAGetProperty @xml, 'Tag', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @xml, 'TagPath', @sTmp0 OUT
        PRINT @sTmp0

        PRINT ''

      END

    -- Produces the following output:

    -- herd
    -- herd
    -- 
    -- species
    -- herd|species
    -- 
    -- animal
    -- herd|species|animal[0]
    -- 
    -- name
    -- herd|species|animal[0]|name
    -- 
    -- birth
    -- herd|species|animal[0]|birth
    -- 
    -- in-date
    -- herd|species|animal[0]|in-date
    -- 
    -- from
    -- herd|species|animal[0]|from
    -- 
    -- gender
    -- herd|species|animal[0]|gender
    -- 
    -- info
    -- herd|species|animal[0]|info
    -- 
    -- animal
    -- herd|species|animal[1]
    -- 
    -- name
    -- herd|species|animal[1]|name
    -- 
    -- birth
    -- herd|species|animal[1]|birth
    -- 
    -- in-date
    -- herd|species|animal[1]|in-date
    -- 
    -- from
    -- herd|species|animal[1]|from
    -- 
    -- gender
    -- herd|species|animal[1]|gender
    -- 
    -- info
    -- herd|species|animal[1]|info
    -- 

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @sbState


END
GO