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