sql - 使用 SQL 遍历 XML

Question

我在 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

但我希望它循环遍历每个值，而不是我插入它应该从中提取的行。

Answer 1

给猫剥皮的另一种方法...

SELECT
       n.value('../@Name', 'varchar(50)'),
       n.value('@Name', 'varchar(50)'),
       n.value('@TPID', 'varchar(50)'),
       n.value('(TPIDs/Phone/@TP)[1]', 'varchar(50)')
from #tmp
CROSS APPLY value.nodes('QueryXMLData/main/C/S') n(n)

Demo