I am, looking for a query to get all attribute values "NetAmount" of Element "PRODUCT" from the following samle XML:

 <CATEGORY name="A">
  <PRODUCT NetAmount="10.50"/>
  <PRODUCT NetAmount="20.50"/>
 <CATEGORY name="B">
  <PRODUCT NetAmount="30.50"/>
  <PRODUCT NetAmount="40.50"/>
  <PRODUCT NetAmount="75.50"/>

For my scenario it does not matter wether the XML is stored in a table or variable.

I tried several different approaches but all failed.


  NetAmount.value('(@NetAmount)[1])','varchar(250)') AS Products
    AS P(NetAmount)

I usually get the following error message that makes no sense to me:

Meldung 2370, Ebene 16, Status 1, Zeile 5 XQuery [value()]: Am Ende des XQuery-Ausdrucks wurden keine Token mehr erwartet. Gefunden: ')'.

1 Answer 1


Almost get it:

    d.lin.value('./@NetAmount', 'decimal(10,2)') as Amount
cross apply
    data.nodes('ROOT/CATEGORY/PRODUCT') as d(lin);
| Amount |
| :----- |
| 10.50  |
| 20.50  |
| 30.50  |
| 40.50  |
| 75.50  |

db<>fiddle here

  • Hi @McNets thanks 1st of all. Q: In case there are additional attributesnamed NetAmount in other elements like for example CATEGORY what might happen? My result returned many more rows than expected. Will those NetAmounts also be contained in the resultset?
    – Magier
    Commented Jun 26, 2019 at 14:20
  • You need another CROSS APPLY join. Here
    – McNets
    Commented Jun 26, 2019 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.