Oracle XMLQuery- Display comma separated list with two XML nodes

Question

I have a query such as this-

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                        <item>
                            <item_detail>AAA</item_detail>
                            <item_amount>1000</item_amount>
                        </item>
                        <item>
                            <item_detail>BBB</item_detail>
                            <item_amount>2000</item_amount>
                        </item>
                    </parent>
                </root>') AS xcol FROM dual)
SELECT xmlcast (
           xmlquery ('root/parent/string-join[item/item_detail/text()]' PASSING xcol RETURNING CONTENT) AS VARCHAR2 (2000))
           AS item_details
  FROM xtbl;

Although the example specified above contains an node, in my real problem not every <parent> node will contain an <item> node. Thus I cannot use the XMLTable solution as described in this answer.

The SELECT xmlcast(... query is what I have tried so far above and it isn't working. My expected output is this-

ITEM_DETAILS
------------------
AAA 1000, BBB 2000

Please suggest how I can modify my XMLQuery.

Answer 1

You can use an XMLTable call still:

SELECT x.*
FROM xtbl
CROSS JOIN xmltable('/root/parent/item'
       PASSING xcol
       COLUMNS item_detail VARCHAR2(10) path 'item_detail',
               item_amount NUMBER path 'item_amount'
) x;

ITEM_DETAI ITEM_AMOUNT
---------- -----------
AAA               1000
BBB               2000

and then concatenate and aggregate:

SELECT listagg(x.item_detail ||' '|| x.item_amount, ', ')
  WITHIN GROUP (ORDER BY item_num) AS item_details
FROM xtbl
CROSS JOIN xmltable('/root/parent/item'
       PASSING xcol
       COLUMNS item_detail VARCHAR2(10) path 'item_detail',
               item_amount NUMBER path 'item_amount',
               item_num FOR ORDINALITY
) x;

ITEM_DETAILS                                      
--------------------------------------------------
AAA 1000, BBB 2000

For a parent with no items that will just return null.

However, you can also do it with an XMLQuery call:

SELECT xmlquery(
  'let $d :=
     for $i in /root/parent/item
       return concat($i/item_detail, " ", $i/item_amount)
   return string-join($d, ", ")'
  PASSING xcol
  RETURNING CONTENT
) AS item_details
FROM xtbl;

ITEM_DETAILS                                      
--------------------------------------------------
AAA 1000, BBB 2000

and again with a parent with no items that just returns null.

The let $d := part concatenates the detail and amount for each item, with a space between them, using concat(). Then all the $d values that generates are aggregated using string-join().

That returns an XMLType, so to get the result as a plain string you can use XMLCast as in your question, or more simply use .getStringVal():

SELECT xmlquery(
  ...
).getStringVal() AS item_details
FROM xtbl;

Answer 2

WITH xtbl AS (SELECT xmltype ('<root>
                    <parent>
                        <item>
                            <item_detail>AAA</item_detail>
                            <item_amount>1000</item_amount>
                        </item>
                        <item>
                            <item_detail>BBB</item_detail>
                            <item_amount>2000</item_amount>
                        </item>
                    </parent>
                </root>') AS xcol FROM dual)
SELECT xmlcast (
           xmlquery (
           'string-join(/root/parent/item/string-join((*), " "), ", ")' 
           PASSING xcol
           RETURNING CONTENT) AS VARCHAR2 (2000)
       )
       AS item_details
  FROM xtbl;

EDIT: There isn't anything special there with *. It is the short hand for child::*

The inner string-join: /root/parent/item/string-join(child::*, " ") joins the values of each child element of /root/parent/item (here it refers to <item_detail> and <item_amount>. the delimiter is a space.

outer string-join for its part, joins the results of each inner string-join with the limiter of a comma.

EDIT-2:

'string-join(/root/parent/item/string-join(
                                           (item_detail, item_amount)
                                           , " "
                                          )
             , ", "
            )'