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

Let's take this XML for example.

<breakfast_menu>
    <food>
        <name>Belgian Waffles</name>
        <name>Cake</name>
        <price>$5.95</price>
        <calories>650</calories>
    </food>
... there will be more types of food so the xml can be longer
    <food>
        <name>Belgian Waffles(2)</name>
        <name>Cake(2)</name>
        <price>$5.95(2)</price>
        <calories>650(2)</calories>
    </food>
</breakfast_menu>

I loaded it into a table XML_T into XML_FILE_DATA column and I can use something like this to retrieve the values.

SELECT X.* 
FROM XML_T, 
    XMLTABLE('/breakfast_menu/food'
             PASSING XML_T.XML_FILE_DATA
             COLUMNS
             "price"       VARCHAR2(50) PATH 'price',
             "calories"    VARCHAR2(50) PATH 'calories',
             ) X;

But If I try to retrieve the <name> like this:

SELECT X.* 
FROM XML_T, 
    XMLTABLE('/breakfast_menu/food'
             PASSING XML_T.XML_FILE_DATA
             COLUMNS
             "name"        VARCHAR2(50) PATH 'name',
             "price"       VARCHAR2(50) PATH 'price',
             "calories"    VARCHAR2(50) PATH 'calories',
             ) X;

I get error ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence. I know this is because there are multiple name values but I don't know how to fix it.

I want to be able to get the 2 names and merge them together in a column like so: Belgian Waffles Cake And even if it will have more than 2 names, maybe 3 or 4 this should just merge together all of them with space between.

CodePudding user response：

You can specify which name you are looking for in your XPath selector like this:

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 TRIM (name1 || ' ' || name2 || ' ' || name3 || ' ' || name4) AS combined_names, x.*
  FROM xml_t,
       XMLTABLE ('/breakfast_menu/food'
                 PASSING xmltype (XML_T.XML_FILE_DATA)
                 COLUMNS name1 VARCHAR2 (50) PATH 'name[1]',
                         name2 VARCHAR2 (50) PATH 'name[2]',
                         name3 VARCHAR2 (50) PATH 'name[3]',
                         name4 VARCHAR2 (50) PATH 'name[4]',
                         price VARCHAR2 (50) PATH 'price',
                         calories VARCHAR2 (50) PATH 'calories') X;



               COMBINED_NAMES                 NAME1      NAME2    NAME3    NAME4       PRICE    CALORIES
_____________________________ _____________________ __________ ________ ________ ___________ ___________
Belgian Waffles Cake          Belgian Waffles       Cake                         $5.95       650
Belgian Waffles(2) Cake(2)    Belgian Waffles(2)    Cake(2)                      $5.95(2)    650(2)