Mark Geerlings
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_labe l_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_ex port_detai l/requirem ent_id'),1 ,30)
,EXTRACTVALUE (VALUE (ltable),'/requirements_ex port_label _detail/la bel_descri ption1')
,EXTRACTVALUE (VALUE (ltable),'/requirements_ex port_label _detail/la bel_descri ption2')
,EXTRACTVALUE (VALUE (dtable),'/requirements_ex port_detai l/requirem ent_date')
,EXTRACTVALUE (VALUE (dtable),'/requirements_ex port_detai l/requirem ent_time')
FROM icaras_orders_xml x,
TABLE (XMLSEQUENCE (EXTRACT (x.inv_doc,'/requirementse xport/requ irements_e xport_head er'))) htable,
TABLE (XMLSEQUENCE (EXTRACT (VALUE (htable),'/requirements_ex port_heade r/requirem ents_expor t_detail') )) dtable,
TABLE (XMLSEQUENCE (EXTRACT (VALUE (dtable),'/requirements_ex port_detai l/requirem ents_expor t_label_de tail'))) ltable
WHERE extractValue(VALUE (dtable),'/requirements_ex port_detai l/requirem ent_type') < '30'
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_ex
,EXTRACTVALUE (VALUE (ltable),'/requirements_ex
,EXTRACTVALUE (VALUE (ltable),'/requirements_ex
,EXTRACTVALUE (VALUE (dtable),'/requirements_ex
,EXTRACTVALUE (VALUE (dtable),'/requirements_ex
FROM icaras_orders_xml x,
TABLE (XMLSEQUENCE (EXTRACT (x.inv_doc,'/requirementse
TABLE (XMLSEQUENCE (EXTRACT (VALUE (htable),'/requirements_ex
TABLE (XMLSEQUENCE (EXTRACT (VALUE (dtable),'/requirements_ex
WHERE extractValue(VALUE (dtable),'/requirements_ex
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This certainly isn't the solution i was hoping for, but I'm accepting it to close the question.
ASKER