Retrieve data from XML CLOB Column in Oracle SQL

Question

I try to retrieve XML Information from a CLOB Column and didn't manage to find suitable examples for the pattern understanding using EXTRACTVALUE() or as it seems to be outdated XMLQUERY() / XMLTABLE(). You may have better example sources than the Oracle documentation (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/XMLQUERY.html#GUID-9E8D3220-2CF5-4C63-BDC2-0526D57B9CDB).

CLOB cell XML_ID1 is filled like this:

<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Susie">
<Parameter Name="Bananas" Value="13649" />
<Parameter Name="Kiwis" Value="26" />
<Parameter Name="Oranges" Value="11210" />
<Parameter Name="Mangos" Value="1793" />
</SelectionCondition>
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Fred">
<Parameter Name="Bananas" Value="206" />
<Parameter Name="Kiwis" Value="45" />
<Parameter Name="Oranges" Value="33300" />
<Parameter Name="Mangos" Value="200" />
</SelectionCondition>
</SelectionConditions>

CLOB cell XML_ID2 is filled like this:

<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Jack">
<Parameter Name="Bananas" Value="1456" />
<Parameter Name="Oranges" Value="9800" />
<Parameter Name="Mangos" Value="17933" />
</SelectionCondition>
</SelectionConditions>

Table structure would be:

ID	ShopName	CLOB_column
1	NY	(XML_ID1)
2	Boston	(XML_ID2)

csv:
ID, ShopName, CLOB_column
1, NY, (XML_ID1)
2, Boston, (XML_ID2)

I would like a result like:

ID	ShopName	Customer	Bananas
1	NY	Susie	13649
1	NY	Fred	206
2	Boston	Jack	1456

Thank you all in advance!

Answer 1

Another version:

WITH data(id, shopname, xml) AS (
    SELECT 1, 'NY', xmltype(q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Susie" />
<Parameter Name="Bananas" Value="13649" />
<Parameter Name="Bananas" Value="13650" />
<Parameter Name="Kiwis" Value="26" />
<Parameter Name="Oranges" Value="11210" />
<Parameter Name="Mangos" Value="1793" />
</SelectionCondition>
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Fred" />
<Parameter Name="Bananas" Value="206" />
<Parameter Name="Kiwis" Value="45" />
<Parameter Name="Oranges" Value="33300" />
<Parameter Name="Mangos" Value="200" />
</SelectionCondition>
</SelectionConditions>}') FROM dual 
UNION ALL
    SELECT 2, 'Boston', xmltype(q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Customer" Value="Jack" />
<Parameter Name="Bananas" Value="1456" />
<Parameter Name="Oranges" Value="9800" />
<Parameter Name="Mangos" Value="17933" />
</SelectionCondition>
</SelectionConditions>}') FROM DUAL 
)
    SELECT id, shopname, customer, banana, orange FROM 
    DATA d,
    xmltable(
        '/SelectionConditions/SelectionCondition' passing d.xml
        columns
            customer varchar2(20) PATH './Parameter[@Name="Customer"]/@Value',
            banana varchar2(2000) PATH 'string-join(./Parameter[@Name="Bananas"]/@Value, ";")',
            orange varchar2(20) PATH 'string-join(./Parameter[@Name="Oranges"]/@Value, ";")'
    ) x1
;


ID|SHOPNAME|CUSTOMER|BANANA     |ORANGE|
--+--------+--------+-----------+------+
 1|NY      |Susie   |13649;13650|11210 |
 1|NY      |Fred    |206        |33300 |
 2|Boston  |Jack    |1456       |9800  |