2

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.

Drew
  • 29,895
  • 7
  • 74
  • 104
Semih Yagcioglu
  • 4,011
  • 1
  • 26
  • 43
  • Your XML snippets are invalid. You're missing a ? at the end of your XML declaration: ``. Also you have multiple root elements, maybe you want to wrap them into a `[...]`? – Jens Erat Nov 09 '11 at 21:26

1 Answers1

0
  • 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")
Jens Erat
  • 37,523
  • 16
  • 80
  • 96
  • sorry for the incorrect syntax. i fixed it, still not working though :) – Semih Yagcioglu Nov 09 '11 at 21:41
  • I'm not sure what semantics of `ora:view` is. The [documentation](http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAIEJDC) only says it creates an XML view over relational data, nothing about updates. I also cannot find any information on this, sadly none confirming my assumptions, either. I guess you will have to create some [permanent XML view](http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAIFJJJ) to use XQuery Update. The query in my answer is fine, I tested it using [BaseX](http://www.basex.org) (replacing `ora:view`). – Jens Erat Nov 09 '11 at 22:58