As promised in my last post, I'll address the parsing error you get when you attempt to convert the text returned by the NCBI efetch utilities into an XMLType. The Oracle XML parser within the database treats the XML comment "<!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd">" as an indicator to validate the XML against the DTD located at "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd". In many, maybe even most, cases this is fine. If you are generating an XML payload you may want to validate it before sending it out to avoid the embarrassment of sending an invalid message. If you are retrieving it from the source, though, odds are good it will be valid and validating it against the DTD may just be extra overhead. It's also possible that the XML parser doesn't understand the DTD. At least one version of the database has a bug related to exactly how that comment is formatted. In my case there are at least two issues related to validating against the DTD, and I have no need for it. Below is the query and the associated error.
'<?xml version="1.0" ?>
<!DOCTYPE TaxaSet PUBLIC "-//NLM//DTD Taxon, 14th January 2002//EN" "https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd">
<Lineage>cellular organisms; Eukaryota; Opisthokonta</Lineage>
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 2
LPX-00261: invalid URL https://www.ncbi.nlm.nih.gov/entrez/query/DTD/taxon.dtd
Fortunately, you can instruct the database to NOT validate.
alter session set events='31156 trace name context forever, level 2'
will instruct the database to skip the validation. You can then use xmlTable, xmlQuery, etc. without receiving an error.
Note: you may need to "grant alter session" to the user in order for this to work, particularly if using an execute immediate within a package to do the alter session command.