The Post was Edited to provide a minimal reproducible example
I'm looking for a way to concatenate all name nodes(could be 2-3-4 etc) together with space between them. The following example gives error ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
. I know this is because there are multiple name nodes.
I tried replacing
"name" VARCHAR2(50) PATH 'name'
with
"name" VARCHAR2(50) PATH 'name[1]'
which would give only the first value. That removes the error but is not concatenating them together.
WITH XML_T (XML_FILE_DATA) AS (SELECT '<breakfast_menu>
<food>
<name>Belgian Waffles</name>
<name>Cake</name>
<price>$5.95</price>
<calories>650</calories>
</food>
<food>
<name>Belgian Waffles(2)</name>
<name>Cake(2)</name>
<price>$5.95(2)</price>
<calories>650(2)</calories>
</food>
</breakfast_menu>' FROM DUAL)
SELECT x.*
FROM XML_T,
XMLTABLE ('/breakfast_menu/food'
PASSING xmltype (XML_T.XML_FILE_DATA)
COLUMNS "name" VARCHAR2(50) PATH 'name',
"price" VARCHAR2(50) PATH 'price',
"calories" VARCHAR2(50) PATH 'calories') X;
I want to be able to get the 2 names and merge them together in a column. Desired Output:
+-------------------------+-------------+-------------+
| name | price | calories |
+-------------------------+-------------+-------------+
| Belgian Waffles Cake | $5.95 | 650 |
| Belgian Waffles Cake(2) | $5.95(2) | 650(2) |
+-------------------------+-------------+-------------+
@Padders Answer solved the problem.
You could make use of Oracle's support for xquery expressions in the PATH string, e.g.
SELECT xt.*
FROM xml_t x,
XMLTABLE (
'breakfast_menu/food'
PASSING x.xml_file_data
COLUMNS
names VARCHAR2 (4000) PATH 'string-join(name," ")',
price VARCHAR2 (50) PATH 'price',
calories VARCHAR2 (50) PATH 'calories') xt;