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!

score 1 · Answer 1 · answered Aug 30 '23 at 17:28

WITH data(xml) AS (
    SELECT q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Apples" Value="1" />
<Parameter Name="Bananas" Value="13649" />
<Parameter Name="Kiwis" Value="26" />
<Parameter Name="Oranges" Value="11210" />
<Parameter Name="Mangos" Value="1793" />
</SelectionCondition>
</SelectionConditions>}' FROM dual 
)
SELECT xmlquery(q'{$v/SelectionConditions/SelectionCondition/Parameter[@Name='Bananas']/@Value}'
    passing xmltype(xml) AS "v" returning content).getStringVal() AS bananas,
    xmlquery(q'{$v/SelectionConditions/SelectionCondition/Parameter[@Name='Oranges']/@Value}'
    passing xmltype(xml) AS "v" returning content).getStringVal() AS oranges
FROM DATA
;

Thank you for the quick answer I was able to adopt it. Unfortunately I noticed the XML sometimes provides multiple Parameters with e.g. Name="Banana" which ends up combining the values to a long number chain. I update the question as it supports better text styling. — Hisager, Aug 30 '23 at 18:00

score 0 · Answer 2 · answered Aug 31 '23 at 06:55

One solution for N nodes, you can use LISTAGG() - it does not aggregate the NULLs - but you need something to group on:

WITH data(id, xml) AS (
    SELECT 1, xmltype(q'{<?xml version="1.0" encoding="utf-16"?>
<SelectionConditions Version="1">
<SelectionCondition Type="Fruits">
<Parameter Name="Apples" Value="1" />
<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>
</SelectionConditions>}') FROM dual 
)
SELECT id, listagg(banana,',') within group(order by banana) as bananas,
    listagg(orange,',') within group(order by orange) as oranges
FROM (
    SELECT id, banana, orange FROM 
    DATA d,
    xmltable(
        '/SelectionConditions/SelectionCondition/Parameter' passing d.xml
        columns
            banana varchar2(20) PATH '.[@Name="Bananas"]/@Value',
            orange varchar2(20) PATH '.[@Name="Oranges"]/@Value'
    ) x1
)

group by id ;

ID bananas   oranges
1   13649,13650 11210

Thank you for the suggestion, but I wan't to use the resulting table as a subquery for further joins and therefore need atomic values. — Hisager, Aug 31 '23 at 13:02

score 0 · Accepted Answer · answered Aug 31 '23 at 15:52

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  |

Thanks again upfront. When I'm trying to adapt it I get this error: ORA-00932: inconsistent datatypes: expected ANYDATA got CLOB You're passing d.xml which you could define earlier, but as I'm trying to read from a database clob column it doesn't work. As I tried it on my own before my post on stackoverflow. That was also the point where I ended. The xml is multinode so xmltable seems to be the applicable function. — Hisager, Aug 31 '23 at 16:07
Ok I managed to solve it by casting the column to xml with xmltype() function and now it runs as intended. Thank you! — Hisager, Aug 31 '23 at 16:30

Retrieve data from XML CLOB Column in Oracle SQL

3 Answers3