Java带loop参数的xml_Loop through XML using SQL

I have this XML in a column in a MSSQL table

I would like for it to return these columns:

Customer Name Subscriber Name TPID Phone TP

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

Michael Ethan 21456 1122334455

Susan Johnathan 110 3344556677

Susan Brad 110 5566778899

I have tried

SELECT value.value('(QueryXMLData/main/C/@SSN)[1]', 'varchar(50)') AS Customer1,

value.value('(QueryXMLData/main/C/S/@SSN)[1]', 'varchar(50)') AS Subb1

from #tmp

But I would like for it to loop through every value instead of me inserting which line it is supposed to extract from.