在XMLTYPE列中插入子节点 [英] Inserting a child node in an XMLTYPE column

问题描述

我有一个简单的查询:

WITH xtbl AS (
  SELECT XMLTYPE ('<a><b>1</b></a>') AS xcol 
    FROM DUAL
)
SELECT XMLQUERY ('copy $tmp := . 
                    modify 
                       insert node <c>2</c>
                         into $tmp/a/b
                       return $tmp'
                 PASSING xcol 
               RETURNING CONTENT) AS newxcol
  FROM xtbl;

我想做的是在节点<a>内的<b>之后插入节点<c>，但是Oracle 12c抛出此错误:

What I'm trying to do is to insert a node <c> after <b> inside the node <a> but Oracle 12c is throwing this error:

ORA-19114: XPST0003 - error during parsing the XQuery expression: 
           LPX-00801: XQuery syntax error at 'EOF'
           5   -
           -  ^

我希望输出为:

NEWXCOL
-------------
<a>
    <b>1</b>
    <c>2</c/>
</a>

我尝试在 Oracle Docs 中寻找替换项appendChildXML并按照该示例进行操作，但仅收到错误消息.

I have tried looking in Oracle Docs for a replacement of appendChildXML and follow that example but only got the error.

我知道这是非常基本的，我缺少明显的东西.请帮忙.

I know it is very basic and I'm missing something obvious. Please help.

推荐答案

以下代码应适用于11和12(已弃用):

Following code should be working for 11 and 12 (deprecated):

SELECT insertXMLafter(XMLType('<a><b>1</b><c>3</c></a>'),
                 '/a/b', XmlType('<c>2</c>'))
  FROM dual;

使用新的XMLQuery语法的相同代码:

Same code using new XMLQuery syntax:

SELECT XMLQuery('copy $tmp := . modify insert node 
                 <c>2</c>
                 after $tmp/a/b 
                 return $tmp'
                PASSING XmlType('<a><b>1</b><c>3</c></a>') RETURNING CONTENT)
  FROM dual;

有关XMLQuery以及旧版本的不推荐使用的功能的更多详细信息，可以在这里找到: http://docs.oracle.com/database/121/ADXDB/app_depr_upd .htm#ADXDB6160

More details regarding XMLQuery and also the old deprecated functions can be found here: http://docs.oracle.com/database/121/ADXDB/app_depr_upd.htm#ADXDB6160

这篇关于在XMLTYPE列中插入子节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！