Skip to content

Instantly share code, notes, and snippets.

@jmelloy
Created February 27, 2013 01:42
Show Gist options
  • Save jmelloy/5044190 to your computer and use it in GitHub Desktop.
Save jmelloy/5044190 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment