{"id":388,"date":"2019-05-08T02:36:24","date_gmt":"2019-05-08T06:36:24","guid":{"rendered":"http:\/\/dbtricks.com\/?p=388"},"modified":"2019-05-15T07:47:42","modified_gmt":"2019-05-15T11:47:42","slug":"a-short-reminder-ora-19279-xpty0004-xquery-dynamic-type-mismatch-expected-singleton-sequence-got-multi-item-sequence","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=388","title":{"rendered":"A short reminder &#8211; ORA-19279: XPTY0004 &#8211; XQuery dynamic type mismatch: expected singleton sequence &#8211; got multi-item sequence"},"content":{"rendered":"<pre lang=\"SQL\" line=\"1\">\r\n\r\nORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence\r\n19279. 00000 -  \"XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence\"<\/pre>\n<p>A short reminder, this message may look intimidating but this is one of the rare cases when oracle codes actually explains the problem well:<\/p>\n<p>*Cause: The XQuery sequence passed in had more than one item.<br \/>\n*Action: Correct the XQuery expression to return a single item sequence.<\/p>\n<p>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.<\/p>\n<p>for example:<br \/>\n(pardon the poor indentation, for some reason the code editor is failing in showing html tags)<\/p>\n<pre lang=\"SQL\" line=\"1\">\r\n SELECT ITEMS\r\nFROM\r\nxmltable(\r\n '*'\r\nPASSING XMLTYPE('<ROWSET>\r\n<ITEMS>\r\n<ITEM>AAA<\/ITEM>\r\n<\/ITEMS>\r\n<ITEMS>\r\n<ITEM>BBB<\/ITEM>\r\n<\/ITEMS>\r\n<\/ROWSET>')\r\nCOLUMNS\r\nITEMS varchar2(10) PATH '\/\/*:ITEM'\r\n)\r\n;<\/pre>\n<p>ORA-19279: XPTY0004 &#8211; XQuery dynamic type mismatch: expected singleton sequence &#8211; got multi-item sequence<\/p>\n<p>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.<\/p>\n<pre lang=\"SQL\" line=\"1\">\r\n SELECT ITEMS\r\nFROM\r\nxmltable(\r\n '\/\/ITEMS'\r\nPASSING XMLTYPE('<ROWSET>\r\n<ITEMS>\r\n<ITEM>AAA<\/ITEM>\r\n<\/ITEMS>\r\n<ITEMS>\r\n<ITEM>BBB<\/ITEM>\r\n<\/ITEMS>\r\n<\/ROWSET>')\r\nCOLUMNS\r\nITEMS varchar2(10) PATH '\/\/*:ITEM'\r\n)\r\n;<\/pre>\n<p>CODES<br \/>\n&#8212;&#8212;&#8212;-<br \/>\nAAA<br \/>\nBBB<\/p>\n","protected":false},"excerpt":{"rendered":"<p>ORA-19279: XPTY0004 &#8211; XQuery dynamic type mismatch: expected singleton sequence &#8211; got multi-item sequence 19279. 00000 &#8211; &#8220;XQuery dynamic type mismatch: expected singleton sequence &#8211; got multi-item sequence&#8221; 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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-388","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=388"}],"version-history":[{"count":25,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/388\/revisions"}],"predecessor-version":[{"id":424,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/388\/revisions\/424"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}