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

Question

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.

While asking a question you need to provide a **minimal reproducible example**: (1) Input XML. (2) Your logic, and SQL that tries to implement it. (3) Desired output based on the #1 above. — Yitzhak Khabinsky, Dec 30 '21 at 16:41
I tried my best to write it, I have the xml, the logic and the sql that I used. The only thing I can think of to do better is to give an example like in the answer I received with the xml included in the PL/SQL but I didn't knew how to include it, because I have it in a table. And the desired output I wrote that I want to merge the nodes into ```Belgian Waffles Cake``` . — Sss, Dec 30 '21 at 17:09
@Yitzhak Khabinsky I will try to make an edit to the post later to make it easier to understand and try to get it closer to a minimal reproducible example. I'm guessing the best would be to have a piece of code that you can copy paste it to test it. But should I make an edit additional to my initial post, or to modify what I wrote and change the way it is? — Sss, Dec 31 '21 at 14:46
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — Yitzhak Khabinsky, Dec 31 '21 at 16:56

score 3 · Accepted Answer · answered Dec 31 '21 at 11:15

3

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;

answered Dec 31 '21 at 11:15

Padders

276
2
5

I think this might be the best way for doing this. It great, easy to understand and it seems to be working no matter how many nodes I have. I'm no expert but this seems better than the first answer – Sss Dec 31 '21 at 14:36
Good answer, +1 from my side! – Yitzhak Khabinsky Dec 31 '21 at 16:58

EJ Egyed · Answer 2 · 2021-12-30T16:54:22.990

2

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)

edited Dec 30 '21 at 16:54

answered Dec 30 '21 at 16:47

EJ Egyed

5,791
1
8
23

I want to have the name columns combined and not have them as sepparate columns. ```"name1" VARCHAR2 (50) PATH 'name[1]', "name2" VARCHAR2 (50) PATH 'name[2]',``` If I delete these 2 columns will it still work? It seems to give error to me – Sss Dec 30 '21 at 16:52
Basically it's good how the result looks, just without name1 and name 2 columns. so it has only 3 columns : COMBINED_NAMES, PRICE and CALORIES – Sss Dec 30 '21 at 16:53
1

All you have to do is modify the columns you are selecting like `SELECT TRIM (name1 || ' ' || name2 || ' ' || name3 || ' ' || name4) AS combined_names, x.price, x.calories FROM....` – EJ Egyed Dec 30 '21 at 16:57
After modifying ```AS combined_names, x.price, x.calories FROM``` it has the desired output. So you put all the columns and you just Select the ones you need. One question: If i have more names i would just continue to add ```nameX VARCHAR2 (50) PATH 'name[X]',``` and so on? Besides that Thanks a lot it helps! :) – Sss Dec 30 '21 at 17:07
1

yes, you can just continue to add additional `nameX` columns to your list of columns in your XMLTABLE expression, then concatenate them in your COMBINED_NAMES column. – EJ Egyed Dec 30 '21 at 17:13

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

2 Answers2