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'