SQL Server

Traverse the XML Tree

See more XML Examples

Demonstrates how to traverse all nodes in an XML tree.

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

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Use the NextInTraversal2 method to traverse all nodes in the XML tree rooted at the caller.

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

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