After reading more carefully through this:

http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

I was able to construct the following query, that gets the results in a set that I can then easily normalize. I understand how this works... I'd like to say I could do this again without effort, but it's tedious. In addition I have no clue whether this is efficient. Efficiency isn't a big deal in my case, since my sets will be small and not high volume, but still would be nice to know.

Here is the solution I came up with:

[font="Courier New"]

select distinct

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

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

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

addressRef.value('@AddressType', 'varchar(10)') as AddressType,

addressRef.value('@City', 'varchar(10)') as City,

addressRef.value('@State', 'varchar(10)') as Zip,

phoneRef.value('@PhoneNumber', 'varchar(15)') as Phone,

phoneRef.value('@PhoneType', 'varchar(15)') as PhoneType

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

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

playerRef.nodes('//AddressList/PlayerAddress/Address') as addresses(addressRef) cross apply

playerRef.nodes('//AddressList/PlayerAddress/Phone') as phones(phoneRef)

order by accountNum, AddressType, Phone

AccountNum AccountStatus PlayerID AddressType City Zip Phone PhoneType

---------- -------------------- ---------- ----------- ---------- ---------- --------------- ---------------

1 AccountStatus1 1 billing City1 State1 PhoneNumber1 Type1

1 AccountStatus1 1 billing City1 State1 PhoneNumber2 Type2

1 AccountStatus1 1 primary City1 State1 PhoneNumber1 Type1

1 AccountStatus1 1 primary City1 State1 PhoneNumber2 Type2

(4 row(s) affected)

[/font]