XML Content Modification in Oracle 11g r2

Question

I've been dealing with a problem in Oracle XML DB (11g R2) as described below:

Say in a table with a XMLType column named "xml_document" I have the following xml document

<?xml encoding="utf-8" ?>
<books>
    <book>
        <author>AUTHORNAME1</author>
        <title>TITLE1</title>
        <price>12.33</price>
    </book>
    <book>
        <author>AUTHORNAME2</author>
        <title>TITLE2</title>
        <price>9.55</price>
    </book>
    <book>
        <author>AUTHORNAME3</author>
        <title>TITLE3</title>
        <price>15.00</price>
    </book>
</books>

Now what I want to do is that replace the titles of all books with "price > 10" as an "-expensive" tag appended.

' for $book in ora:view("XML_TABLE")//books/book where $book/price > 10 return replace value of node $book/title with concat($book/title/text(),"-expensive") '

So after I execute the query in Oracle SQLDeveloper the resulting XML content will be as the following.

<?xml encoding="utf-8" ?>
<books>
    <book>
        <author>AUTHORNAME1</author>
        <title>TITLE1-expensive</title>
        <price>12.33</price>
    </book>
    <book>
        <author>AUTHORNAME2</author>
        <title>TITLE2</title>
        <price>9.55</price>
    </book>
    <book>
        <author>AUTHORNAME3</author>
        <title>TITLE3-expensive</title>
        <price>15.00</price>
    </book>
</books>

I have already tried to do it with UPDATEXML(), XMLQUERY() and XMLTABLE() procedures and still cannot take a step forward.

Any help will be appreciated.

Answer 1

• when is no XQuery statement (though I cannot tell about Oracle XQuery). Try where.
• String concatenation is done using concat(str1, [...], str2), not the +-operator.

Try this query:

for $book in ora:view("XML_TABLE")//book
where $book/price > 10
return
  replace value of node $book/title
  with concat($book/title/text(), "-expensive")