Skip to content
Advertisement

Extracting multiple values from BLOB as XML

I have an XML like this in a BLOB column:

As you can see the year can be either in the event level or at country level. There can be multiple events and multiple countries per event. This whole XML is stored in a BLOB column in Oracle. I need to extract the value of the year or better check if the year is 2000 and if so return the primary key of the row.

I used EXISTSNODE to check if the year tag is present.

However this fails and the extractvalue query returns multiple nodes, so I changed the parameter to '/Document/history/Event[1]/year/text()' to check and it works. However this wouldnt be enough as it only checks the first event tag.

I looked at other questions here and one of the options was to use XMLTABLE since extractvalue is deprecated. I am having trouble understanding the parameters given inside the XMLTABLE. Could someone explain how to use XMLTABLE in this scenario? I should point out that the original datatype is BLOB and not CLOB. Thank you.

Advertisement

Answer

Use XMLTABLE to get values for both locations and then use COALESCE to show whichever is not NULL:

Which, for the sample data:

Outputs:

YEAR | LOCATION | TYPE         
---: | :------- | :------------
1983 | Lisbon   | Political    
2016 | USA      | Entertainment

db<>fiddle here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement