SQL Server

Get XML Attribute Value by Path

See more XML Examples

Demonstrates how to get an attribute value by path. 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

    -- The XML used in this example contains the following:
    -- This example will get the value of the "spay-neuter" attribute for each pig.

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

    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'

    -- Use the ChilkatPath method to return the value of a particular attribute at a given path.
    -- A path ending with "(attributeName)" will return the value for the given attribute.
    DECLARE @path nvarchar(4000)
    SELECT @path = 'herd|species|animal[0]|gender|(spay-neuter)'
    DECLARE @attrValue nvarchar(4000)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @attrValue OUT, @path

    PRINT '1st pig spay-neuter: ' + @attrValue

    SELECT @path = 'herd|species|animal[1]|gender|(spay-neuter)'
    EXEC sp_OAMethod @xml, 'ChilkatPath', @attrValue OUT, @path

    PRINT '2nd pig spay-neuter: ' + @attrValue

    -- We could also do it like this:
    SELECT @path = 'herd|species|animal[i]|gender|(spay-neuter)'
    DECLARE @i int
    SELECT @i = 0
    EXEC sp_OAMethod @xml, 'NumChildrenAt', @iTmp0 OUT, 'herd|species'
    WHILE @i < @iTmp0
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i


        EXEC sp_OAMethod @xml, 'ChilkatPath', @sTmp0 OUT, @path
        PRINT 'spay-neuter for pig number ' + @i + 1 + ': ' + @sTmp0
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @xml


END
GO