Oracle 11g r2 中的 XML 内容修改

发布于 2024-12-14 08:07:01 字数 1684 浏览 0 评论 0原文

我一直在处理 Oracle XML DB (11g R2) 中的一个问题，如下所述：

在一个名为“xml_document”的 XMLType 列的表中，我有以下 xml 文档

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

现在我想做的是替换所有带有“价格> 10”作为附加“-昂贵”标签的书籍。

' 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") '

因此，在 Oracle SQLDeveloper 中执行查询后，生成的 XML 内容将如下所示。

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

我已经尝试使用 UPDATEXML()、XMLQUERY() 和 XMLTABLE() 过程来完成此操作，但仍然无法向前迈出一步。

任何帮助将不胜感激。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

世界如花海般美丽 2024-12-21 08:07:01

when 不是 XQuery 语句（尽管我无法说出 Oracle XQuery）。尝试在哪里。
字符串连接是使用 concat(str1, [...], str2) 完成的，而不是使用 + 运算符。

尝试这个查询：

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")

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")

回复收藏 0 原文

~没有更多了~