我有一个MSSQL表中的一列包含XML数据
<QueryXMLData>
<main ShowAllPhoneNumbers="yes">
<C ID="5753768" HID="1" Name="Michael" SSN="xxxxxxxxxxx" PayType="" Status="Active" StatusID="1">
<S ID="5483911" HID="3" Name="Ethan" SSN="xxxxxxxxxxx" CType="Subscription" TPID="21456" TPName="Outside" TPShortName="Out" Status="Active" StatusID="P" D="Y" Checked="yes" Found="yes">
<TPIDs>
<Phone TP="1122334455" />
</TPIDs>
</S>
</C>
<C ID="5670554" HID="1" Name="Susan" SSN="xxxxxxxxxxx" PayType="" Status="Active" StatusID="1">
<S ID="5297452" HID="3" Name="Johnathan" SSN="xxxxxxxxxxx" CType="Outbound" TPID="110" TPName="Out" TPShortName="Inside" Status="Active" StatusID="1" D="Y" Checked="yes" Found="yes">
<TPIDs>
<Phone TP="3344556677" />
</TPIDs>
</S>
<S ID="5297426" HID="3" Name="Brad" SSN="xxxxxxxxxxx" CType="Outbound" TPID="110" TPName="Out" TPShortName="Inside" Status="Active" StatusID="1" D="Y" Checked="yes" Found="yes">
<TPIDs>
<Phone TP="5566778899" />
</TPIDs>
</S>
</C>
</main>
</QueryXMLData>
我希望它能返回以下列:
Customer Name Subscriber Name TPID Phone TP
--------------------------------------------------------------
Michael Ethan 21456 1122334455
Susan Johnathan 110 3344556677
Susan Brad 110 5566778899
我已经尝试过
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
但我希望它可以循环遍历每个值,而不是我插入应该从哪一行提取。