OOW 2012 – XQuery Update (HOL)

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(
         '<posummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
            $XML/PurchaseOrder/User,
            $XML/PurchaseOrder/Requestor,
            $XML/PurchaseOrder/LineItems/LineItem[2]
          }
          </posummary>'
         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(
         '<posummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
            $XML/PurchaseOrder/User,
            $XML/PurchaseOrder/Requestor,
            $XML/PurchaseOrder/LineItems/LineItem[2]
          }
          </posummary>'
         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(
         '<posummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
            $XML/PurchaseOrder/LineItems/LineItem[2]
          }
          </posummary>'
         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(
                                  '<lineitem ItemNumber="4">
                                     <part Description="Rififi" UnitPrice="29.95">37429155622</part>
                                     <quantity>2</quantity>
                                   </lineitem>'
                                ) 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(
         '<posummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
            $XML/PurchaseOrder/LineItems/LineItem[3]
          }
          </posummary>'
         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(
                                   '<lineitems>
                                      <lineitem ItemNumber="1">
                                        <part Description="The Secret of Roan Inish" UnitPrice="19.95">43396509290</part>
                                        <quantity>7</quantity>
                                      </lineitem>
                                      <lineitem ItemNumber="2">
                                        <part Description="The Mean Season" UnitPrice="19.95">27616861177</part>
                                        <quantity>5</quantity>
                                      </lineitem>
                                      <lineitem ItemNumber="3">
                                        <part Description="Irma La Douce" UnitPrice="19.95">27616865908</part>
                                        <quantity>4</quantity>
                                      </lineitem>
                                    </lineitems>'
                                 ) 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(
         '<posummary lineItemCount="{count($XML/PurchaseOrder/LineItems/LineItem)}">{
            $XML/PurchaseOrder/User,
            $XML/PurchaseOrder/Requestor,
            $XML/PurchaseOrder/LineItems/LineItem[2]
          }
          </posummary>'
         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