• 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?