October 28

Getting started with XQuery Update Facility 1.0

DeleteXML, InsertXML, UpdateXML, appendChildXML, insertChildXML, insertchildXMLafter, insertChildXMLbefore, insertXMLafter and insertXMLbefore are dead (& deprecated) from Oracle 12.1 and onwards. Long live the King, ehhh, XQuery Update Facility.

The XQuery Update facility should be used from 11.2.0.3 and onwards…

In principle it’s a good thing that XQuery also now (since 2011) has a update facility, so it extents the XQuery language with more power than only reporting or query functionality. That doesn’t mean that it is easy, at least for me, to learn, once again, new syntax.

Hereby some of my learning experiences to master this update to the XQuery language.

From a database perspective

First of all, all modifications (delete, insert, update) in the XML content, are based on using the relational SQL statement “update”. So no “delete”, no “insert” SQL statements are used to manipulate the XML content. This makes sense of course if you think about it, but initially relational thinking wise, being a database person, it might feel odd. If you want to delete something, you use delete, right? Anyway, you can use “delete” or “insert” if you want DML operations on the whole XML document but not when manipulating content in the XML content.

Of course with the relational SQL syntax, you can filter out XML rows that don’t need any manipulation. Let’s check out a problem I needed solving.

The following XML document has code values that are empty and I needed them to be removed from the XML document.

<?xml version="1.0" encoding="UTF-8"?>
<md:opslag-metadata xmlns:md="http://www.somewhere.nl/puma/metadata">
   <md:commentaar>
   <md:actualiteitsdatum>1996-01-01</md:actualiteitsdatum>
   <md:publicatiedatum>2010-09-01</md:publicatiedatum>
   <md:publiceerbaar>true</md:publiceerbaar>
   <md:redactioneeldocumenttype code="1"/>
   <md:taal code="1"/>
   <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code=""/>
   <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code=""/>
   <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code="6787"/>
   <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code="6231"/>
   <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code="8129"/>
   <md:vakgebied code="682"/>
   <md:weergavetitel>Wijzigingsgegevens</md:weergavetitel>
 </md:commentaar>
</md:opslag-metadata>

The table that held the XML content IC_METADATA has also a relational ID column and CORRECTIEID column

describe bck_ic_metadata

Name        Null     Type 
----------- -------- ------------- 
ID          NOT NULL NUMBER(38) 
METADATA             XMLTYPE() 
CORRECTIEID NOT NULL VARCHAR2(128)

Filter first, do stuff later…

So nothing prevents me from filtering out most data via SQL, via (for example):

  • AND correctieid like ‘M-1405-0277%’

The above statement filters out all modifications made with this tag and stored in this backup table, but of course I also only need the incorrect ones with empty code values: code=””. So for the following step I need to search and filter XML content in column METADATA. For this bit of filtering and sorting out results I don’t need, I need a XML solution in the form or XMLEXISTS. The problem with a powerful language like XQuery or SQL is (of course) that there are multiple solutions to achieve your goal. It doesn’t have to be XMLEXISTS but for my example, it will suffice. If you have a look at XMLEXISTS, you will see that you can use XPath (v.2) or XQuery constructs.

XMLEXISTS ( XQuery_string [ XML_passing_clause ] )

XML_passing_clause:
PASSING [ BY VALUE ] expr [ AS identifier ] [, expr [ AS identifier ] ]...

I want only to remove the nodes/elements with code=”” so the following:

  • <md:trefwoord xmlns:md=”http://www.somewhere.nl/puma/metadata” code=””/>

Here is were the namespace trouble starts. I can only find /remove/search for this kind of data if I add the correct namespace alias references for “md:” and the namespace URI/URN reference. I mentioned “trouble”, because the functions and operators have minuscule differences where those locations are added (and I never can remember them by hart). Namespace declarations in XMLEXIST, for the example here, need to resolve the md and  xmlns to be set and resolved.

  
WHERE XMLExists('declare default element namespace 
                 "http://www.somewhere.nl/puma/metadata"; (::)
  $p/opslag-metadata/commentaar/trefwoord[@code=""]'
  PASSING b.metadata AS "p")

The (::) is added in the default (for elements) namespace declaration so my client, for example SQL*Plus, doesn’t  interpret the semi-colon ( ; ) as the end of the code (which would resolve in SQL syntax errors at that point). Everything between the two single quotes is the XQuery statement. That can be something troublesome if you have to work with single quotes in your XQuery statement, for example, creating structured XML Indexes. As an alternative you can make use of the SQL quote notation alternative, supported from Oracle 10 and upwards and especially in the Oracle XML database realm of things, makes life a bit easier:

 
WHERE XMLExists( q'[ declare default element namespace
                 "http://www.somewhere.nl/puma/metadata"; (::)
  $p/opslag-metadata/commentaar/trefwoord[@code=""] ]'
  PASSING b.metadata AS "p")
 
WHERE XMLExists( q'# declare default element namespace
                 "http://www.somewhere.nl/puma/metadata"; (::)
  $p/opslag-metadata/commentaar/trefwoord[@code=""] #'
  PASSING b.metadata AS "p")

Passing the XML column content to bind value “p”, gives us the opportunity to follow the XQuery syntax a bit more strict, so I now actually can search for empty code attribute values via

  • $p/opslag-metadata/commentaar/trefwoord[@code=””

In my case I not only have to use XMLEXISTS for finding empty code attribute values, but also to distinct between different XML Schema restricted XML content. That is, I can have XML content that are /opslag-metadata/commentaar/ XML documents, but also /opslag-metadata/artikel/ XML documents.

Both “storage/format” descriptions are defined, and more, in the same XML Schema (http://www.somewhere.nl/puma/metadata) but if I wouldn’t explicitly set the namespace and XPath, I would get an XQuery error saying that, for instance, “element commentaar does not exist”.

So in short my XML Schema defines the “physical” XML layout, not the logical layout. An “artikel” (article) can have a “commentaar” (comment) which can have a comment that refers to a article that…etc. There is no, afaik, XML schema that defines how many comments an article can have and/or how they are interlinked or how it fits (where/place) in an article. As always, such a “design”, makes it flexible (and error prone)… Anyway…

XQuery Update – transform!

In principle with the SQL and XQuery conditions in place, you are now able to delete or insert rows from the table where exists in the XML content, “where code attribute is empty” (defined via alias “md”, namespace <md:opslag-metadata xmlns:md=”http://www.somewhere.nl/puma/metadata”>)   and “correctieid like ‘M-1405-0277%’ ” or ID equals…or whatever.

That is not our goal. We want to piece wise update / remove the incorrect empty code attribute sections.

Oracle uses the transform definition of the XQuery Update Facility for this.

As always this is easily explained on www.w3.org (not), so therefore this extra addition to it, hopefully sharing a bit more light in the subject 😉

The “transform” section of XQuery Update is described as follows:

TransformExpr ::= "copy" "$" VarName ":=" ExprSingle ("," "$" VarName ":=" ExprSingle)* "modify" ExprSingle "return" ExprSingle

In my case the full statement would be (to get rid of the empty attribute sections)

'declare default element namespace 
         "http://www.somewhere.nl/puma/metadata"; (::)
copy $i := $p modify
delete nodes $i/opslag-metadata/commentaar/trefwoord[@code=""]
return $i'

In short

  • Start with the namespace declaration (if needed)

…and don’t forget this, otherwise the rows will be “updated” but, in most cases, nothing will have changed…and you have no idea how many times I have forgotten namespaces…

  • copy $ {variable-01} := $ {variable-02} modify

…Oracle only uses the transform section of the XQuery update. In an attempt to describe copy/modify in labor terms: “find the content, found via the statement in the rest of the XQuery statement, and label it “variable-01” then “replace/modify the variable-02 content with the variable-01 content” based on… {variable-01} is set by the following XQuery/XPath code, {variable-02} is set via the PASSING clause (normally…)

  • delete nodes

 …this section declares what you want to do via…the following XPath or XQuery statement. In my case I want to delete the empty code attribute nodes. Oracle follows the www.w3.org XQuery Update operations. So in short the insert / delete / replace / rename syntax will follow:

InsertExpr ::= "insert" ("node" | "nodes") SourceExpr InsertExprTargetChoice TargetExpr
DeleteExpr ::= "delete" ("node" | "nodes") TargetExpr
ReplaceExpr ::= "replace" ("value" "of")? "node" TargetExpr "with" ExprSingle
RenameExpr ::= "rename" "node" TargetExpr "as" NewNameExpr
  • $i/opslag-metadata/commentaar/trefwoord[@code=””]

…this section has the actual XPath or expression that defines what/which node(s) have to be deleted / replaced / renamed / inserted.

  • return $i

$i…{variable-01}…returns the result so it can be used to modify the contents of {variable-02}

 “Easy” right? Nope, not really. At least I think so. You have an enormous amount of new possibilities, and therefore it is also easier to forget stuff or to make errors. Therefore test, test, test, and test again, your XQuery statement, so you know for sure it is doing the right stuff and results are as expected…

In my case I could test portions or the statement or check via using XMLQUERY:

SELECT XMLQuery(q'[ declare default element namespace 
  "http://www.somewhere.nl/puma/metadata"; (::)
  copy $i := $p modify
  delete nodes $i/opslag-metadata/commentaar/trefwoord[@code=""]
  return $i
  ]'
  PASSING b.metadata AS "p" RETURNING CONTENT)
  FROM bck_ic_metadata b
WHERE XMLExists( q'[ declare default element namespace 
  "http://www.somewhere.nl/puma/metadata"; (::)
  $p/opslag-metadata/commentaar/trefwoord[@code=""]
  ]'
PASSING b.metadata AS "p");


The full XQuery Update statement for me, deleting all nodes with empty attribute values will be:


UPDATE pumatmp.bck_ic_metadata b 
  SET b.metadata = 
  XMLQuery(q'[ declare default element namespace 
                   "http://www.somewhere.nl/puma/metadata"; (::)
  copy $i := $p modify
  delete nodes $i/opslag-metadata/commentaar/trefwoord[@code=""]
  return $i
  ]'
  PASSING b.metadata AS "p" RETURNING CONTENT)
 WHERE XMLExists( q'[ declare default element namespace 
                      "http://www.somewhere.nl/puma/metadata"; (::)
  $p/opslag-metadata/commentaar/trefwoord[@code=""]
  ]'
  PASSING b.metadata AS "p")
  AND b.correctieid like 'M-1405-0277%'
;


After executing this update, the XML document given at the start of this post, will look like:


<?xml version="1.0" encoding="UTF-8"?>
<md:opslag-metadata xmlns:md="http://www.somewhere.nl/puma/metadata">
  <md:commentaar>
  <md:actualiteitsdatum>1996-01-01</md:actualiteitsdatum>
  <md:publicatiedatum>2010-09-01</md:publicatiedatum>
  <md:publiceerbaar>true</md:publiceerbaar>
  <md:redactioneeldocumenttype code="1"/>
  <md:taal code="1"/>
  <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code="6787"/>
  <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code="6231"/>
  <md:trefwoord xmlns:md="http://www.somewhere.nl/puma/metadata" code="8129"/>
  <md:vakgebied code="682"/>
  <md:weergavetitel>Wijzigingsgegevens</md:weergavetitel>
  </md:commentaar>
</md:opslag-metadata


I hope this shed some light in how to read and/or construct a XQuery Update statement which is the preferred method in 12.1 as alternative to the deprecated functions: DeleteXML, InsertXML, UpdateXML, appendChildXML, insertChildXML, insertchildXMLafter, insertChildXMLbefore, insertXMLafter and insertXMLbefore…


HTH


Marco


PS – Thanks Marc for providing, from time to time, the oh so needed sanity checks.


Oracle given examples


Have a look at the Oracle OOW 2012 Hands-on Lab examples: XQuery Update HOL


…or the examples given in the “Deprecated Functions” section of the Oracle XMLDB Developers Guide for 12.1 (for easy reference also copied here):






Original Expression
Replacement Expression








-- Insert a node as the last child of a node.
UPDATE warehouses SET warehouse_spec =
  appendChildXML(
    warehouse_spec,
    '/Warehouse/Parking',
    XMLType('<Spaces>250</Spaces>'));





-- Insert a node as the last child of a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node 
            <Spaces>250</Spaces>
            as last into $tmp/Warehouse/Parking
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Delete a node.
UPDATE warehouses SET warehouse_spec =
  deleteXML(value(po), '/Warehouse/VClearance');





-- Delete a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify delete node
            $tmp/Warehouse/VClearance return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Insert a node as a child of a node.
UPDATE warehouses SET warehouse_spec =
  insertChildXML(
    warehouse_spec,
    '/Warehouse/Parking',
    'Spaces',
    XMLType('<Spaces>300</Spaces>');





-- Insert a node as a child of a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node
            <Spaces>300</Spaces>
            into $tmp/Warehouse/Parking
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Insert a node before a node.
UPDATE warehouses SET warehouse_spec = 
  insertXMLbefore(
    warehouse_spec, 
    '/Warehouse/RailAccess', 
    XMLType('<SkyAccess>N</SkyAccess>');





-- Insert a node before a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node
            <SkyAccess>N</SkyAccess>
            before $tmp/Warehouse/RailAccess 
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Insert a node after a node.
UPDATE warehouses SET warehouse_spec = 
  insertXMLafter(
    warehouse_spec,
    '/Warehouse/RailAccess', 
    XMLType('<SkyAccess>N</SkyAccess>');





-- Insert a node after a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify insert node 
            <SkyAccess>N</SkyAccess>
            after $tmp/Warehouse/RailAccess 
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Replace the text value of a set of nodes.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec,  
            '/Warehouse/Building/text()',
            'Owned');





-- Replace the text value of a set of nodes.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify
            (for $i in $tmp/Warehouse/Building/text()  
             return replace value of node $i
                    with ''Owned'') 
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Insert a child under each node in a collection.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec = 
  insertChildXML(
    warehouse_spec, 
    '/Warehouse/Building',
    'Owner', 
    XMLType('<Owner>LesserCo</Owner>'));





-- Insert a child under each node in a collection.
-- (Assumes a collection of <Building> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify
            (for $i in $tmp/Warehouse/Building
             return insert node       
             <Owner>LesserCo</Owner>
             into $i)
            return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Insert as child before the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec = 
  insertChildXMLbefore(
    warehouse_spec, 
    '/Warehouse/Building[1]',
    'Owner', 
    XMLType('<Owner>LesserCo</Owner>'));





-- Insert as child before the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery(
    'copy $tmp := . modify
     (for $i in $tmp/Warehouse/Building[1]/Owner
      return insert node <Owner>LesserCo</Owner>
             before $i)
     return $tmp'
    PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Insert as child after the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec =
  insertChildXMLafter(
    warehouse_spec, 
    '/Warehouse/Building[1]',
    'Owner', 
    XMLType('<Owner>LesserCo</Owner>'));





-- Insert as child after the other children.
-- (Assumes a collection of <Owner> nodes.)
UPDATE warehouses SET warehouse_spec =
  XMLQuery(
    'copy $tmp := . modify
     (for $i in $tmp/Warehouse/Building[1]/Owner
      return insert node <Owner>LesserCo</Owner>
             after $i)
     return $tmp'
    PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Delete a node.
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec,
            '/Warehouse/Docks',
            NULL);





-- Delete a node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := . modify delete node 
            $tmp/Warehouse/Docks return $tmp'
           PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Replace with an empty node.
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec, '/Warehouse/Docks', '' );





-- Replace with an empty node.
UPDATE warehouses SET warehouse_spec =
  XMLQuery('copy $tmp := $p1 modify
            (for $j in $tmp/Warehouse/Docks
             return replace node $j with $p2)
            return $tmp'
           PASSING warehouse_spec "p1", '' AS "p2" 
           RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;









-- Update multiple paths.
UPDATE warehouses SET warehouse_spec = 
  updateXML(warehouse_spec,  
            '/Warehouse/Docks/text()', '4',
            '/Warehouse/Area/text()', '3500');





-- Update multiple paths.
UPDATE warehouses SET warehouse_spec =
  XMLQuery(
    'copy $tmp := . modify
     ((for $i in $tmp/Warehouse/Docks/text()  
       return replace value of node $i with 4),
      (for $i in $tmp/Warehouse/Area/text()  
       return replace value of node $i with 3500))
     return $tmp'
    PASSING warehouse_spec RETURNING CONTENT)
  WHERE warehouse_spec IS NOT NULL;








							

		

				

						
Categories12cR1, 12.1.0.1 SQL, XML/SQL, XPath, XQuery
			
TagsXQuery Update
		

		

	

		Marco Gralike		
			Written by: