As promised, hereby the Hands-On Lab Oracle OpenWorld XQuery Update example statements. The following XQuery Update code will work from Oracle database 11.2.0.3 and upwards and is fully supported from this version onwards. For more information see some of the blogposts here on this site or the OTN XMLDB forum for more examples. The code shown here was used and demonstrated during the Oracle OpenWorld 2012 Hands-on Labs:
--
-- 1. Simple XQuery showing the current state of the document
--
select XMLQUERY(
'{
$XML/PurchaseOrder/User,
$XML/PurchaseOrder/Requestor,
$XML/PurchaseOrder/LineItems/LineItem[2]
}
'
passing object_value as "XML"
returning CONTENT
) INITIAL_STATE
from PURCHASEORDER
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
--
-- 2. Modifying the content of existing nodes using XQuery Update.
--
update PURCHASEORDER
set object_value = XMLQuery
(
'copy $NEWXML := $XML modify (
for $PO in $NEWXML/PurchaseOrder return (
replace value of node $PO/User with $USERID,
replace value of node $PO/Requestor with $FULLNAME,
replace value of node $PO/LineItems/LineItem/Part[@Description=$OLDTITLE]/@Description with $NEWTITLE
)
)
return $NEWXML'
passing object_value as "XML",
'KCHUNG' as "USERID",
'Kelly Chung' as "FULLNAME",
'The Mean Season' as "OLDTITLE",
'The Wizard of Oz' as "NEWTITLE"
returning content
)
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]/LineItems/LineItem/Part[@Description=$OLDTITLE]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF",
'The Mean Season' as "OLDTITLE"
)
/
--
-- 3. Simple XQuery showing the updates to the document
--
select XMLQUERY(
'{
$XML/PurchaseOrder/User,
$XML/PurchaseOrder/Requestor,
$XML/PurchaseOrder/LineItems/LineItem[2]
}
'
passing object_value as "XML"
returning CONTENT
) UPDATED_NODES
from PURCHASEORDER
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
--
-- 4. Deleting a node using XQuery update.
--
update PURCHASEORDER
set object_value = XMLQuery(
'copy $NEWXML := $XML modify (
delete nodes $NEWXML/PurchaseOrder/LineItems/LineItem[@ItemNumber=$ITEMNO]
)
return $NEWXML'
passing object_value as "XML", 2 as ITEMNO
returning CONTENT
)
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
--
-- 5. Simple XQuery showing the updates to the document
--
select XMLQUERY(
'{
$XML/PurchaseOrder/LineItems/LineItem[2]
}
'
passing object_value as "XML"
returning CONTENT
) DELETED_NODE
from PURCHASEORDER
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
--
-- 6. Inserting a node using XQuery update.
--
update PURCHASEORDER
set object_value = XMLQuery(
'copy $NEWXML := $XML modify (
for $TARGET in $NEWXML/PurchaseOrder/LineItems/LineItem[@ItemNumber="3"]
return insert node $LINEITEM after $TARGET
)
return $NEWXML'
passing object_value as "XML",
xmlType(
'
37429155622
2
'
) as "LINEITEM"
returning CONTENT
)
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
--
-- 7. Simple XQuery showing the updates to the document
--
select XMLQUERY(
'{
$XML/PurchaseOrder/LineItems/LineItem[3]
}
'
passing object_value as "XML"
returning CONTENT
) INSERTED_NODE
from PURCHASEORDER
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
--
-- 8. Undo all the above changes using XQuery Update
--
update PURCHASEORDER
set object_value = XMLQuery(
' copy $NEWXML := $XML modify (
for $PO in $NEWXML/PurchaseOrder return (
replace value of node $PO/User with $USERID,
replace value of node $PO/Requestor with $FULLNAME,
replace node $PO/LineItems with $LINEITEMS
)
)
return $NEWXML'
passing object_value as "XML",
'AFRIPP' as "USERID",
'Adam Fripp' as "FULLNAME",
xmlType(
'
43396509290
7
27616861177
5
27616865908
4
'
) as "LINEITEMS"
returning content
)
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
--
-- 9. Simple XQuery showing the updates to the document
--
select XMLQUERY(
'{
$XML/PurchaseOrder/User,
$XML/PurchaseOrder/Requestor,
$XML/PurchaseOrder/LineItems/LineItem[2]
}
'
passing object_value as "XML"
returning CONTENT
) FINAL_STATE
from PURCHASEORDER
where xmlExists(
'$XML/PurchaseOrder[Reference=$REF]'
passing object_value as "XML",
'AFRIPP-20120430212831873PDT' as "REF"
)
/
HTH
Marco
