0

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

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

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.

Sample:

SELECT 
  NetAmount.value('(@NetAmount)[1])','varchar(250)') AS Products
FROM 
  @x.nodes('//ROOT/Category/Product') 
    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: ')'.

2

Almost get it:

select
    d.lin.value('./@NetAmount', 'decimal(10,2)') as Amount
from
    t
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 Jun 26 at 14:20
  • You need another CROSS APPLY join. Here – McNets Jun 26 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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