[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1134
  • Last Modified:

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'
Mark Geerlings
Mark Geerlings
  • 2
1 Solution
Interesting question,  short answer no.

Can you create dummy records on the fly a sort of NVL for nodes?

I'm not entirely sure how that would look, nor am I confident it will solve the double xml read.

Could you pre-process the data and include a "label_detail_exist" column?  Either in a view, inline view or modify the table and then union the two queries together?

Or create the dummy records at insert/update time.

No idea if that's practical or not for your application, but it would at least allow for a sql solution.
Mark GeerlingsDatabase AdministratorAuthor Commented:
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.
Mark GeerlingsDatabase AdministratorAuthor Commented:
This certainly isn't the solution i was hoping for, but I'm accepting it to close the question.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now