ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 - "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
A short reminder, this message may look intimidating but this is one of the rare cases when oracle codes actually explains the problem well:
*Cause: The XQuery sequence passed in had more than one item.
*Action: Correct the XQuery expression to return a single item sequence.
Basically, it only means that there is more than one instance of the tag you are looking at the level you are looking. Or, in plain English, duplicate nodes.
for example:
(pardon the poor indentation, for some reason the code editor is failing in showing html tags)
SELECT ITEMS
FROM
xmltable(
'*'
PASSING XMLTYPE('
- AAA
- BBB
')
COLUMNS
ITEMS varchar2(10) PATH '//*:ITEM'
)
;
ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence
One solution, is to point the XQuery_string to the correct level. In our example, all you need to do is start our query at the CODES level.
SELECT ITEMS
FROM
xmltable(
'//ITEMS'
PASSING XMLTYPE('
- AAA
- BBB
')
COLUMNS
ITEMS varchar2(10) PATH '//*:ITEM'
)
;
CODES
———-
AAA
BBB
Hello,
How can I use this example if the xml is the value of a xmltype column in a table?
I get the same error with this query:
select t.id, a.regel
from table_name t,
xmltable(‘/root/company/addresses/address/rows’
passing t.xml_data
columns regel path ‘row’) a
order by t.id;
Thank you.
Carlos