(SQL Server) Parsing XML Children Having Identical Tags

Demonstrates how to deal with XML where child elements may have identical tags.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example will get the phoneNumber's and groupMembershihpInfo's from the following XML:

    -- <?xml version="1.0" encoding="UTF-8" ?>
    -- <someEntries>
    --     <entry>
    --         <id>123</id>
    --         <updated>2017-07-19T05:19:57.761Z</updated>
    --         <app:edited xmlns:app="http://www.w3.org/2007/app">2017-07-19T05:19:57.761Z</app:edited>
    --         <category scheme="http://schemas.google.com/g/2005#kind" term="http://schemas.google.com/contact/2008#contact"/>
    --         <title>George Costanza</title>
    --          <gd:name>
    --             <gd:fullName>George Costanza</gd:fullName>
    --             <gd:givenName>George</gd:givenName>
    --             <gd:familyName>Costanza</gd:familyName>
    --         </gd:name>
    --         <gd:phoneNumber rel="http://schemas.google.com/g/2005#home">(555) 123-4567</gd:phoneNumber>
    --         <gd:phoneNumber rel="http://schemas.google.com/g/2005#mobile">(555) 444-8877</gd:phoneNumber>
    --         <gd:phoneNumber rel="http://schemas.google.com/g/2005#work">(555) 678-1111</gd:phoneNumber>
    --         <gContact:groupMembershipInfo deleted="false" href="http://www.google.com/123"/>
    --         <gContact:groupMembershipInfo deleted="false" href="http://www.google.com/456"/>
    --     </entry>
    -- </someEntries>
    -- 

    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/georgeCostanza.xml'

    DECLARE @numPhoneNumbers int
    EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @numPhoneNumbers OUT, 'entry|*:phoneNumber'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numPhoneNumbers
      BEGIN
        DECLARE @xPhoneNumber int
        EXEC sp_OAMethod @xml, 'GetNthChildWithTag', @xPhoneNumber OUT, 'entry|*:phoneNumber', @i
        EXEC sp_OAGetProperty @xPhoneNumber, 'Content', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xPhoneNumber

        SELECT @i = @i + 1
      END

    PRINT '----'

    DECLARE @numGroupMemberships int
    EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @numGroupMemberships OUT, 'entry|*:groupMembershipInfo'
    SELECT @i = 0
    WHILE @i < @numGroupMemberships
      BEGIN
        DECLARE @xMembership int
        EXEC sp_OAMethod @xml, 'GetNthChildWithTag', @xMembership OUT, 'entry|*:groupMembershipInfo', @i
        EXEC sp_OAMethod @xMembership, 'GetAttrValue', @sTmp0 OUT, 'href'
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xMembership

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @xml


END
GO