Oracle 12c: XQuery Update

New, new…? No, not really new, XQuery Update (W3C standard/draft 2011) was already implemented in 11.2.0.3.0, but is now officially also announced. Besides the XQuery Full Text support (XQFT for short, W3C standard/draft 2011), this is one of the big new features in Oracle 12c. With this update XQuery functionality, the XQuery standard is further expanded in a mature query language for XML (/unstructured) information.

Now also in Oracle 12c, this has immediate consequences for following, implementing a XQuery standard across the board. The following features, functions have now been given the status “deprecated” (most of them introduced in Oracle version 10.2) for updating XML data:

All Oracle SQL functions for updating XML data are deprecated. Oracle recommends that you use XQuery Update instead. These are the deprecated XML updating functions:

updateXML

insertChildXML

insertChildXMLbefore

insertChildXMLafter

insertXMLbefore

insertXMLafter

appendChildXML

deleteXML

Although being signaled “deprecated”, be aware that they are still supported, but Oracle move to a position that the new XQuery Update functionality will be the basis for further (performance) improvements and after a while will be the sole solution to update XML content. Following along of the XQuery standard is not that surprising, especially noticing very interesting movements regarding XQuery extensions like JSoniq (JSON Extension for XQuery – have a go at it in the Zorba demo environment). This will make interesting standard architectural solutions available for all kinds unstructured data environment.

A small example of an update of XML content could now be done via:

--
-- 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"
       )
/

Have a further look at some of the Oracle OpenWorld Hands-on Lab Oracle OpenWorld 2012 Hands-on Lab Examples or the Oracle 12c XMLDB Developers Guide.