Oracle Database 12c: XQuery Update Screen Shot 2013 06 28 at 12.01.40

Oracle Database 12c: XQuery Update

New, new…? No, not really new, XQuery Update (W3C standard/draft 2011) was already implemented in, 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.

Screen Shot 2013-06-28 at 12.01.40
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:


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.

Screen Shot 2013-06-28 at 12.22.35

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

-- Modifying the content of existing nodes using XQuery Update.
   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(
          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.