It's interesting that there isn't more responders... I read into that that everyone hates this XML stuff.

From what I can see, what makes this especially difficult is that for any given player, there are multiple addresses, and for any given address there are multiple phones. However, the only key value for an address (and therefore the way to relate the Phones to the Address) is the AddressType attribute, that is part of the Address node, which is a sibling of the Phone nodes. It would have been much better to include those attributes in the parent PlayerAddress node (and not have the Address node). Then the key AddressType value could have been associated with the Phones. Unfortunately, this is what I've been given to work with.

The only resolution I see is a RBAR solution. First get a result set that includes the attributes for each Player as columns, and a column with the XML fragment for the PlayerAddresses. You can do that with this:

select

accountRef.value('@AccountNum', 'varchar(10)') AS AccountNum,

accountRef.value('@AccountStatus', 'varchar(20)') AS AccountStatus,

playerRef.value('@PlayerID', 'varchar(10)') as PlayerID,

playerRef.value('@FirstName', 'varchar(10)') as FirstName,

playerRef.value('@LastName', 'varchar(10)') as LastName,

playerRef.query('AddressList') as AddressList

into #tempTbl

from @myXML.nodes('/AccountDetailsRsp') as account(accountRef) cross apply

accountRef.nodes('//PlayerInfo') as player(playerRef)

Then, for each player,

1. count the number of PlayerAddress nodes

2. using the row filter, query for each PlayerAddress node individually, parsing the details and storing

I hope not to have to do that... hopefully someone else knows a better way.