Navigate multiple nodes Xquery xml SQL Server 2019

Question

Hi i have the current XML:

<?xml version="1.0" encoding="utf-8"?>
<tcpos-export>
  <transactions>
    <transaction>
        <code>1</code>
        <description>MAXI MERCADO SELECTO</description>
      </shop>

        <code>2</code>
        <description>MAÑANITA PANADERIA</description>
      </till>
      <cashier>
        <code>2004</code>
        <description>NELSON PANADERIA</description>
        <first-name>NELSON PANADERIA</first-name>
      </cashier>

      <trans-item type="article">
        <hash-code>832129</hash-code>
        <code>30237</code>
        <quantity>1</quantity>
        <unit-price>0.50</unit-price>
        <total-price>0.50</total-price>
     
      </trans-item>
      <payment type="cash">
        <code>01</code>
        <description>Efectivo</description>
        <description-translations />
        <amount>0.50</amount>
        <prepayment>false</prepayment>
        <signature-required>false</signature-required>
        <payment-timestamp>18.08.2021 06:51:27</payment-timestamp>
        <cash-change>4.50</cash-change>
        <cash-given>5.00</cash-given>
      </payment>

with this xquery sql 2019:

--procesar articulos del dia de venta
SELECT 
        trans.value('(shop/description/text())[1]','varchar(100)') tienda,
        trans.value('(till/code/text())[1]','varchar(100)') caja,
        trans.value('(cashier/code/text())[1]','varchar(100)') cajero,
        trans.value('(beginning-timestamp/text())[1]','varchar(100)') fecha,
        trans.value('(trans-num/text())[1]','varchar(100)') transaccion,
        item.value('(code/text())[1]','varchar(100)') itemcode,
        item.value('(description/text())[1]','varchar(100)') description,
     
        item.value('(hash-code/text())[1]','int') hashcode

FROM [dbo].[XmlImport] xi

CROSS APPLY xi.[LoadedXML].nodes('tcpos-export/transactions/transaction') x1(trans)

CROSS APPLY x1.trans.nodes('trans-item[
    hash-code/text() and
    not( unit-price[contains(text()[1], "-")] ) and
    not( taxable-amount[contains(text()[1], "-")] ) and
    not( delete-operator-id/text() )
]') x2(item)

CROSS APPLY (VALUES (
    item.value('(quantity/text())[1]','numeric(10,3)'),
    item.value('(weight/text())[1]','numeric(10,3)'),
    item.value('(vat-code/text())[1]','varchar(100)')
) ) v(quantity, weight, vatcode)

ORDER BY hashcode;

im already getting those columns correctly from transactions node example cashier, till. In adition im getting info from node. But, im confused in how i can get info from the next node

<payment type="cash">
        <code>01</code>
        <description>Efectivo</description>
        <description-translations />

i would like to get code and description

i tried the following:

adding this as additional cross apply

CROSS APPLY xi.[LoadedXML].nodes('tcpos-export/transactions/transaction') x3(payments)

CROSS APPLY x3.payments.nodes('payment[
    code/text() 
]') x4(payment)

and in select at the end:

payment.value('(code/text())[1]','varchar(100)')

but when i run the query it stays running and running, and got freezed. I dont know what else i can do.

Please give me some tips.

thank you

Is there always only one `Payment` node? – Charlieface Sep 01 '21 at 09:50 — Charlieface, Sep 01 '21 at 09:50

score 1 · Accepted Answer · answered Sep 01 '21 at 09:56

1

You have two options:

You can just select the values directly in your select

SELECT 
        trans.value('(shop/description/text())[1]','varchar(100)') tienda,
        trans.value('(payment/amount/text())[1]','numeric(18,2)') caja,
        -- etc....

FROM [dbo].[XmlImport] xi
-- etc....

Alternatively, you can do another CROSS APPLY nodes. This is what you were trying to do, but it should be x1.trans.nodes('payment') because you want to break out each Payment node from transaction, not from the root XML.

Note that the VALUES clause I added in answer to your previous question was needed only because we needed to refer multiple times to the same node.

SELECT 
        trans.value('(shop/description/text())[1]','varchar(100)') tienda,
        trans.value('(till/code/text())[1]','varchar(100)') caja,
        trans.value('(cashier/code/text())[1]','varchar(100)') cajero,
        trans.value('(beginning-timestamp/text())[1]','varchar(100)') fecha,
        trans.value('(trans-num/text())[1]','varchar(100)') transaccion,
        item.value('(code/text())[1]','varchar(100)') itemcode,
        item.value('(description/text())[1]','varchar(100)') description,
     
        item.value('(hash-code/text())[1]','int') hashcode
        payment.value('(code/text())[1]','int') paymentCode
FROM [dbo].[XmlImport] xi

CROSS APPLY xi.[LoadedXML].nodes('tcpos-export/transactions/transaction') x1(trans)

CROSS APPLY x1.trans.nodes('trans-item[
    hash-code/text() and
    not( unit-price[contains(text()[1], "-")] ) and
    not( taxable-amount[contains(text()[1], "-")] ) and
    not( delete-operator-id/text() )
]') x2(item)

CROSS APPLY x1.trans.nodes('payment') x3(payment)

ORDER BY hashcode;

Note that if there might be no payment node then you need OUTER APPLY not CROSS APPLY

answered Sep 01 '21 at 09:56

Charlieface

52,284
6
19
43

and what you recommend? by selecting directly or creating a new node like you did? theres any diff between them in terms of flexibility? – alexistkd Sep 01 '21 at 12:56
Yes i did not understand that one from my previous post why you put a values clause and not use them directly? its because im using CASE statements? – alexistkd Sep 01 '21 at 13:00
1

If you select directly from `LoadedXML` then you will get *all* `payment` nodes, even ones from other `transaction` nodes, you need it to be correlated. As I said, the previous question needed to refer to those values `quantity` and `weight` multiple times, so they were places in a `CROSS APPLY (VALUES`. See also https://stackoverflow.com/questions/65818438/possible-to-store-value-of-one-select-column-and-use-it-for-the-next-one/65818648#65818648 – Charlieface Sep 01 '21 at 13:03
also i saw instead of where clauses you use always cross apply instead right? what about group by? can i user it like that last Order by? – alexistkd Sep 01 '21 at 13:24
1

It's usually faster to filter XML directly within XQuery. To use `GROUP BY` you need `CROSS APPLY (VALUES` because you would be referring to the same value twice. Again, you want each `payment` node *as it relates to its parent `transaction` node* you don't want *all* `payment` nodes from the whole XML against every single `transaction`. Try both and you'll see the difference. – Charlieface Sep 01 '21 at 13:29
And you right, i need to get all payment nodes from that .XML file as im using it to create one big invoice in my ERP system. I need to send all items, quantity, unitprice, taxes. Thats why i asked why not use select directly from Loaded XML – alexistkd Sep 01 '21 at 13:29
Sorry, but how i can filter directly from xquery? for example i need to GROUP by this value '(trans-num/text())[1]','varchar(100)') and sum all payment amount values (payment/amount/text() into one row group by trans-num and other fields – alexistkd Sep 01 '21 at 13:31

Navigate multiple nodes Xquery xml SQL Server 2019

1 Answers1