gistfile1.sql

select order_id, order_Revision, order_revision_date, revision_closed, SITE_ID, member_id,
    EXTRACTVALUE(order_xml,'/Order/OrderHeader/HandlingMethod','xmlns="http://schemas.drugstore.com/OPERA"') shipping_method,
    XMLCast(XMLQuery('declare default element namespace "http://schemas.drugstore.com/OPERA"; /Order/OrderHeader/PlacedDate' passing order_xml returning content) as varchar2(400)) placedDate
from orders 
    join ORDERS_TIPS using (order_id)
where ORDER_ID = 10000604275
 and orders.order_revision = order_revision_tip
go
select order_id, order_Revision, order_revision_date, 
    EXTRACTVALUE(order_xml,'/Order/OrderHeader/HandlingMethod','xmlns="http://schemas.drugstore.com/OPERA"') shipping_method,
    suborders.*
from orders 
    join ORDERS_TIPS using (order_id),
    XMLTable(xmlnamespaces(default 'http://schemas.drugstore.com/OPERA'),
                '/Order/FulfillmentGroups/FulfillmentGroup'
                   PASSING order_xml
                   columns suborder_id  path '@dcTransmissionId',
                           total path 'FulfillmentGroupTotal/Summary/Total',
                           shipped path 'FullfillmentGroupHeader/FulfillmentStatus/IsShipped'
                  ) suborders
where ORDER_ID = 10000604275
 and orders.order_revision = order_revision_tip