xmloracle-databasexml-parsingoracle-sqldeveloperxmltable

Oracle XMLTABLE Function. How to merge nodes that have the same name?


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.


Solution

  • 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;