Link to home
Start Free TrialLog in
Avatar of Mark Geerlings
Mark GeerlingsFlag for United States of America

asked on

Need outer-join like functionality for extracting XML data

This statement worked well for us in an XML-to-Oracle interface we have, but now we have a problem.  Some of the new records do not have the lowest level in the heirarchy at all (the "ltable", or: "/requirements_export_label_detail" section).  This query works something like a standard join in Oracle, so when one of the 'tables" does not have matching records, the other data for that record is not returned - that is a problem!

Does Oracle offer anything like an "outer join" operator to handle this situation with xml?  I know that I can run two separate statements, one that includes the join to "ltable" and one that doesn't but then I also need to add an "if" statement in my procedure to detect this condition, and I then get the performance penalty of having to read the xml data twice, once including the "ltable" data, and once without.

select SUBSTR (EXTRACTVALUE (VALUE (dtable),'/requirements_export_detail/requirement_id'),1,30)
,EXTRACTVALUE (VALUE (ltable),'/requirements_export_label_detail/label_description1')
,EXTRACTVALUE (VALUE (ltable),'/requirements_export_label_detail/label_description2')
,EXTRACTVALUE (VALUE (dtable),'/requirements_export_detail/requirement_date')
,EXTRACTVALUE (VALUE (dtable),'/requirements_export_detail/requirement_time')
FROM icaras_orders_xml x,
TABLE (XMLSEQUENCE (EXTRACT (x.inv_doc,'/requirementsexport/requirements_export_header'))) htable,
TABLE (XMLSEQUENCE (EXTRACT (VALUE (htable),'/requirements_export_header/requirements_export_detail'))) dtable,
TABLE (XMLSEQUENCE (EXTRACT (VALUE (dtable),'/requirements_export_detail/requirements_export_label_detail'))) ltable
WHERE extractValue(VALUE (dtable),'/requirements_export_detail/requirement_type') < '30'
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark Geerlings

ASKER

We ended up having to use the only option I know of,  that is: use two separate statements, one that includes all three levels of detail, and one that includes only two levels, and incur the processing penalty of having to read the *.xml data twice.
This certainly isn't the solution i was hoping for, but I'm accepting it to close the question.