如何在SQL Server XML查询中为支付记录添加序号列

嘿，这事儿其实用SQL Server的窗口函数就能轻松搞定！咱们结合你提供的XML数据和预期结果，直接上完整的实现代码：

-- 声明XML变量（补全了必要节点以便完整演示）
DECLARE @XML AS XML= N'<order xmlns="somenamspace/2006-10-31" order-no="1235645">
  <order-date>2017-07-24T20:48:57.000Z</order-date>
  <payments>
    <payment>
      <payment-method>credit-card</payment-method>
      <card-type>VISA</card-type>
      <amount>325.4800</amount>
    </payment>
    <payment>
      <payment-method>gift-card</payment-method>
      <card-type>CD</card-type>
      <amount>325.4800</amount>
    </payment>
    <payment>
      <payment-method>gift-card</payment-method>
      <card-type>MC</card-type>
      <amount>325.4800</amount>
    </payment>
  </payments>
</order>';

-- 处理XML命名空间并查询，添加序号列
WITH XMLNAMESPACES(DEFAULT 'somenamspace/2006-10-31')
SELECT
  @XML.value('(/order/@order-no)[1]', 'VARCHAR(20)') AS order_no,
  T.c.value('(payment-method)[1]', 'VARCHAR(20)') AS payment_method,
  T.c.value('(card-type)[1]', 'VARCHAR(20)') AS card_type,
  T.c.value('(amount)[1]', 'DECIMAL(10,4)') AS amount,
  -- 按XML中支付节点的出现顺序生成严格的序号
  ROW_NUMBER() OVER (ORDER BY T.c.value('let $node := . return count(../*[. << $node]) + 1', 'INT')) AS serial_number
FROM @XML.nodes('/order/payments/payment') T(c);

关键细节说明： #

• WITH XMLNAMESPACES：你的XML带有默认命名空间，必须先声明它，否则SQL Server会找不到对应的节点，这是处理带命名空间XML的必备步骤。
• ROW_NUMBER()窗口函数：这里通过XML节点的位置计算逻辑（count(../*[. << $node]) + 1）生成序号，能100%保证序号和XML里支付记录的顺序完全一致；如果场景对顺序要求没那么严苛，也可以简化写成ROW_NUMBER() OVER (ORDER BY (SELECT NULL))，大多数情况下也能得到正确结果。

运行后你会得到和预期完全匹配的结果： #

内容的提问来源于stack exchange，提问作者Pinaki Mukherjee