Your query doesn't match the sample data. That said you're on the right track. You just didn't get the cross apply structures quite right.
Assuming you do it this way - you will get the appropriate data joins:
declare @xml xml
set @xml=cast('<AccountDetailsRsp AccountNum="1" AccountStatus="AccountStatus1">
<PlayerInfo PlayerID="1" FirstName="FirstName1" LastName="LastName1"> <AddressList> <PlayerAddress> <Address AddressType="primary" City="City1" State="State1" Zip="Zip1"/> <FutureUse /> <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" /> <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" /> </PlayerAddress> <PlayerAddress> <Address AddressType="billing" Zip="Zip2" State="State2" City="City2" /> <FutureUse /> <Phone PhoneNumber="PhoneNumber3" PhoneType="Type3" /> </PlayerAddress> </AddressList> </PlayerInfo>
<PlayerInfo PlayerID="2" FirstName="FirstName1" LastName="LastName1"> <AddressList> <PlayerAddress> <Address AddressType="primary" City="City1" State="State1" Zip="Zip1"/> <FutureUse /> <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" /> <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" /> </PlayerAddress> <PlayerAddress> <Address AddressType="billing" Zip="Zip2" State="State2" City="City2" /> <FutureUse /> <Phone PhoneNumber="PhoneNumber3" PhoneType="Type3" /> </PlayerAddress> </AddressList> </PlayerInfo>
</AccountDetailsRsp>' as XML)
selectact.value('(@AccountNum)[1]','Int') account,
player.value('(@PlayerID)[1]','Int') play,
c.value('(Address/@AddressType)[1]','nvarchar(100)') addrtype,
d.value('(@PhoneNumber)[1]','nvarchar(100)') phone
from @xml.nodes('AccountDetailsRsp') account(act) cross apply
act.nodes('PlayerInfo') p(player)
cross apply player.nodes('AddressList/PlayerAddress') x(c)
cross apply c.nodes('Phone') y(d) --this need to be relative to PlayerAddress, NOT above
Notice that I am stopping just shy of going to PlayerAddress within the cross apply, so you can point out that you are joining based on their mutual relation to playerAddress. I could throw in another cross apply if need be but it isn't necessary in this case.
Edit: typoed the node name they both join by.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?