A short reminder – ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence

1
2
3
 
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"

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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 SELECT ITEMS
FROM
xmltable(
 '*'
PASSING XMLTYPE('<ROWSET>
<ITEMS>
<ITEM>AAA</ITEM>
</ITEMS>
<ITEMS>
<ITEM>BBB</ITEM>
</ITEMS>
</ROWSET>')
COLUMNS
ITEMS varchar2(10) PATH '//*:ITEM'
)
;

SELECT ITEMS FROM xmltable( '*' PASSING XMLTYPE('<ROWSET> <ITEMS> <ITEM>AAA</ITEM> </ITEMS> <ITEMS> <ITEM>BBB</ITEM> </ITEMS> </ROWSET>') 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 SELECT ITEMS
FROM
xmltable(
 '//ITEMS'
PASSING XMLTYPE('<ROWSET>
<ITEMS>
<ITEM>AAA</ITEM>
</ITEMS>
<ITEMS>
<ITEM>BBB</ITEM>
</ITEMS>
</ROWSET>')
COLUMNS
ITEMS varchar2(10) PATH '//*:ITEM'
)
;

SELECT ITEMS FROM xmltable( '//ITEMS' PASSING XMLTYPE('<ROWSET> <ITEMS> <ITEM>AAA</ITEM> </ITEMS> <ITEMS> <ITEM>BBB</ITEM> </ITEMS> </ROWSET>') COLUMNS ITEMS varchar2(10) PATH '//*:ITEM' ) ;

CODES
———-
AAA
BBB

One thought on “A short reminder – ORA-19279: XPTY0004 – XQuery dynamic type mismatch: expected singleton sequence – got multi-item sequence

  1. Carlos Wiesemann

    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

Leave a Reply

Your email address will not be published. Required fields are marked *