Oracle Database 12c: XQuery Update

0

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.

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:

    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.

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.
--
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.

Share.

About Author

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

Leave a Reply