Need outer-join like functionality for extracting XML data

Posted on 2007-10-18
Last Modified: 2010-04-21
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'
Question by:markgeer
    LVL 73

    Accepted 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.
    LVL 34

    Author Comment

    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.
    LVL 34

    Author Closing Comment

    This certainly isn't the solution i was hoping for, but I'm accepting it to close the question.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now