selecting one value out of an xml column

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)');