Sql-server – XQquery for specific attribute of all specific elements in XML

sql serversql server 2014xmlxquery

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: ')'.

Best Answer

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

Related Solutions

T-sql – How to remove xmlns from child elements with FOR XML

Vote on this connect item for a change of behavior.

You can use for xml explicit to build your XML instead.

Something like this:

;with DummyTable(id) as 
(
  select 1 union all
  select 2
)

select 1 as tag,
       0 as parent,
       'http://schemas.xmlsoap.org/soap/envelope/' as [soapenv:Envelope!1!xmlns:soapenv],
       'http://foobar' as [soapenv:Envelope!1!xmlns:pas],
        null as [pas:Child!2!pas:ID]
union all
select 2,
       1,
       null,
       null,
       id
from DummyTable       
for xml explicit

Result:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="http://foobar">
  <pas:Child pas:ID="1" />
  <pas:Child pas:ID="2" />
</soapenv:Envelope>

Sql-server – IN and NOT IN for XML column

The exist() Method (xml Data Type) returns a bit.
1 if at least one node is found and 0 if no nodes are found (empty result set).

To get the rows where neither ABC or XYZ exist you just have to compare the result of exist with 0.

[myxmlcolumn].exist('for $x in /Root/Row 
                     where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) 
                     return $x') = 0

Your FLWOR query can be rewritten using a predicate on the user node instead,

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 0

And for the IN version of the query you check if exist returns 1 instead.

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 1

Best Answer

Related Solutions

T-sql – How to remove xmlns from child elements with FOR XML

Sql-server – IN and NOT IN for XML column

Related Question