How to extract relational data from XML in SQL Server

Question

Hello, I'm new to XML world. I'm having a problem with XML data type in SQL Server 2005. I've a stored procedure that expects XML parameter. The value of the parameter as given below...

DECLARE @productIds xml

SET @productIds =
    '<ROOT>
       <AGENTLIST>
         <AGENT>
           <HOSTAPPLICATIONRECORDKEY>GRI</HOSTAPPLICATIONRECORDKEY>
           <BROKERID>02h21a0539</BROKERID>
           <BROKERSHARE>25</BROKERSHARE>
           <BROKERSTATE>AK</BROKERSTATE>
           <CARRIERCODE>GRIC</CARRIERCODE>
           <PLANCODE>EP02</PLANCODE>
           <COVERAGECODES>
               <COVERAGECODE>TL50IM06</COVERAGECODE>
               <COVERAGECODE>TL50IM10</COVERAGECODE>
           </COVERAGECODES>
           <SPLITBROKERID1>2</SPLITBROKERID1>
           <SPLITBROKERSHARE1>25</SPLITBROKERSHARE1>
           <SPLITBROKERID2></SPLITBROKERID2>
           <SPLITBROKERSHARE2>25</SPLITBROKERSHARE2>
           <SPLITBROKERID3></SPLITBROKERID3>
           <SPLITBROKERSHARE3>25</SPLITBROKERSHARE3>
           <APPSIGNEDDATE>7/31/2009</APPSIGNEDDATE>
         </AGENT>
         <AGENT>
           <HOSTAPPLICATIONRECORDKEY>GRI</HOSTAPPLICATIONRECORDKEY>
           <BROKERID>02h21a0538</BROKERID>
           <BROKERSHARE>25</BROKERSHARE>
           <BROKERSTATE>AK</BROKERSTATE>
           <CARRIERCODE>GRIC</CARRIERCODE>
           <PLANCODE>EP02</PLANCODE>
           <COVERAGECODES>
               <COVERAGECODE>TL50IM07</COVERAGECODE>
               <COVERAGECODE>TL50IM11</COVERAGECODE>
           </COVERAGECODES>
           <SPLITBROKERID1>2</SPLITBROKERID1>
           <SPLITBROKERSHARE1>25</SPLITBROKERSHARE1>
           <SPLITBROKERID2></SPLITBROKERID2>
           <SPLITBROKERSHARE2>25</SPLITBROKERSHARE2>
           <SPLITBROKERID3></SPLITBROKERID3>
           <SPLITBROKERSHARE3>25</SPLITBROKERSHARE3>
           <APPSIGNEDDATE>7/31/2009</APPSIGNEDDATE>
         </AGENT>
       </AGENTLIST>
     </ROOT>'

My stored procedure should return all Broker IDs and their corresponding Coverage Code(s) for each of the Broker...

Any help would be highly appreciated

Answer 1

How does this work for you??

SELECT 
    PID.NDS.value('(BROKERID)[1]', 'varchar(50)') AS 'BrokerID',
    CCS.CC.value('(.)[1]', 'varchar(50)') AS 'CoverageID'
FROM
    @productIds.nodes('/ROOT/AGENTLIST/AGENT') AS PID(NDS)
CROSS APPLY
    PID.NDS.nodes('COVERAGECODES/COVERAGECODE') AS CCS(CC)

I get a result of:

BrokerID    CoverageID
02h21a0539  TL50IM06
02h21a0539  TL50IM10
02h21a0538  TL50IM07
02h21a0538  TL50IM11

Is that what you're looking for??

The best intro to this XML "trickery" in SQL Server 2005 and up can be found in this article "SQL Server 2005 XQuery and XML-DML" by Alex Homer.

Marc