sqlsql-serverxmlqxmlquery

SQL Server XML query adding a sequence number

I have a sample query which basically loop through payments and output the below result. I need to add a new column sequence number and populate sequence values. How can I achieve that.

enter image description here

1235645 credit-card VISA 325.4800   1
1235645 gift-card   CD   325.4800   2
1235645 gift-card   MC   325.4800   3

** expected result: Sequence number last column above:

DECLARE @XML AS XML=
N'<order xmlns="somenamspace/2006-10-31" order-no="1235645">
  <order-date>2017-07-24T20:48:57.000Z</order-date>
  <original-order-no>00000001</original-order-no>
  <customer>
    <customer-name>abcd abcd</customer-name>
    <customer-email>jjj@gmail.com</customer-email>
  </customer>
  <current-order-no>1235645</current-order-no>
  <payments>
    <payment>
      <credit-card>
        <card-type>VISA</card-type>
        <card-number>XXXX-XXXX-XXXX-1111</card-number>
        <card-holder>abcd</card-holder>
      </credit-card>
      <gift-card>
        <card-type>CD</card-type>
        <card-number>465795</card-number>
      </gift-card>
      <gift-card>
        <card-type>MC</card-type>
        <card-number>2345678</card-number>
      </gift-card>
      <amount>325.48</amount>
    </payment>
  </payments>
</order>';

WITH XMLNAMESPACES(DEFAULT N'somenamspace/2006-10-31')
    SELECT 
        @xml.value(N'(/order/@order-no)[1]',N'int') AS OrderNumber,
        p.value(N'local-name(.)',N'nvarchar(max)') AS PaymentType,
        p.value(N'(card-type/text())[1]','nvarchar(max)') AS CardType,
        p.value(N'(../amount/text())[1]','decimal(10,4)') AS Amount
    FROM 
        @xml.nodes(N'/order/payments/payment/*[local-name()!="amount"]') AS A(p)
Solution

  • Might be it's this you are looking for:

    WITH XMLNAMESPACES(DEFAULT N'somenamspace/2006-10-31')
SELECT
    ROW_NUMBER() OVER(PARTITION BY p.value(N'local-name(.)',N'nvarchar(max)') ORDER BY (SELECT NULL)) AS SequenceNumber, 
    @xml.value(N'(/order/@order-no)[1]',N'int') AS OrderNumber,
    p.value(N'local-name(.)',N'nvarchar(max)') AS PaymentType,
    p.value(N'(card-type/text())[1]','nvarchar(max)') AS CardType,
    p.value(N'(../amount/text())[1]','decimal(10,4)') AS Amount
FROM 
    @xml.nodes(N'/order/payments/payment/*[local-name()!="amount"]') AS A(p)

    You can remove the PARTITION BY p.value(N'local-name(.)',N'nvarchar(max)'). With this it will restart the counter for each PaymentType, without this, you'd just get your payments numbered from 1 to n.