Question

I have a particularly troublesome xml column to query data from. The schema is fixed by the Quebec Ministry of Revenue so "it is what it is"

The important part of the query looks like this:

with XMLNAMESPACES(default  http://www.mrq.gouv.qc.ca/T5 )
select xmldata.value( (//Groupe02/*/Montants/B_PrestREER_FERR_RPDB)[1] ,  decimal(16,2) );

The wildcard between Groupe02 and Montants could be any one of A D R or T. (that s right one letter element names)

Trouble is I need to query to only select A, D or R nodes but not T nodes. as a first guess I tried:

with XMLNAMESPACES(default  http://www.mrq.gouv.qc.ca/T5 )
select @x.value( (//Groupe02/A|D|R/Montants/B_PrestREER_FERR_RPDB)[1] ,  decimal(16,2) );

but that failed with the error "The XQuery syntax union is not supported"

What other possible ways are there to make such a query.

Answer 1

I think using [name()!= T ] might work. E.g.

select @x.value("(//Groupe02/*[name()!= T ]/Montants/B_PrestREER_FERR_RPDB)[1]",  decimal(16,2) );