文档首页/ 云数据库 GaussDB/ 开发指南（主备版_8.x）/ 存储过程/ 高级包/ 二次封装接口（推荐）/ DBE_XMLGEN
更新时间：2024-06-03 GMT+08:00
查看PDF
分享

DBE_XMLGEN

接口介绍

DBE_XMLGEN系统包将SQL查询的结果转换为规范的XML格式，并将结果返回。支持的所有接口参考表2 DBE_XMLGEN。

表1 DBE_XMLGEN数据类型

类型名称

描述

DBE_XMLGEN.CTXHANDLE

用于存储XML输出状态的数据类型。
  1. 在同一个session中context handle最多只允许存在65535个。关闭context handle并不会回收这个数量。
  2. 输出的xml中表字段、类型与用户创建的表字段与类型大小写一致，如果需要大写字段与类型名需要在创建时用双引号包裹，显示指定。
  3. NEWCONTEXTFROMHIERARCHY初始化时，使用SETNULLHANDLING、USENULLATTRIBUTEINDICATOR、SETCONVERTSPECIALCHARS方法设置但是不生效。
表2 DBE_XMLGEN

接口名称

描述

DBE_XMLGEN.CONVERT

将输入的字符串进行xml编码或解码操作。

DBE_XMLGEN.NEWCONTEXT

初始化普通context handle。

DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY

初始化带有递归元素的context handle。

DBE_XMLGEN.SETCONVERTSPECIALCHARS

设置输出的xml是否需要xml编码。

DBE_XMLGEN.SETNULLHANDLING

设置xml中null值如何展示。

DBE_XMLGEN.SETROWSETTAG

设置xml根节点名称。

DBE_XMLGEN.SETROWTAG

设置xml中每一行数据的tag名。

DBE_XMLGEN.USENULLATTRIBUTEINDICATOR

对xml中的null值所在的元素添加xsi:nil="true"属性。

DBE_XMLGEN.USEITEMTAGSFORCOLL

对数组类型变量所在的元素中添加'_item'后缀。

DBE_XMLGEN.GETNUMROWSPROCESSED

查看上一次getxml或者getxmltype返回的数据行数。

DBE_XMLGEN.SETMAXROWS

设置getxml最大的返回行数。

DBE_XMLGEN.SETSKIPROWS

设置跳过sql行数。

DBE_XMLGEN.RESTARTQUERY

重启sql。

DBE_XMLGEN.GETXMLTYPE

返回XMLTYPE类型的xml文本。

DBE_XMLGEN.GETXML

返回CLOB类型的xml文本。

DBE_XMLGEN.CLOSECONTEXT

关闭context handle。
  • DBE_XMLGEN.CONVERT

    将输入的字符串进行xml编码或解码操作。

    会按以下规则进行转换

    表3 XML编码规则

    原始值

    编码值

    &

    &

    <

    &lt;

    >

    &gt;

    "

    &quot;

    '

    &apos;

    函数原型：

    DBE_XMLGEN.CONVERT(XMLSTR IN VARCHAR2, FLAG IN NUMBER := 0) RETURNS VARCHAR2;
DBE_XMLGEN.CONVERT(XMLCLOB IN CLOB, FLAG IN NUMBER := 0) RETURNS CLOB;

    参数说明：

    表4 DBE_XMLGEN.CONVERT接口参数说明

    参数

    描述

    XMLSTR

    需要转换的XML字符串，VARCHAR2类型。

    XMLCLOB

    需要转换的XML字符串， CLOB类型。

    FLAG

    转码或解码字符串。

    0：编码操作。

    1：解码操作。

    示例：

    -- xml解码
SELECT DBE_XMLGEN.CONVERT('<foo/>', 1);
 convert 
---------
 <foo/>
(1 row)
-- xml编码
SELECT DBE_XMLGEN.CONVERT('<foo><qwe</foo>', 0);
            convert             
--------------------------------
 &lt;foo&gt;&lt;qwe&lt;/foo&gt;
(1 row)
  • DBE_XMLGEN.NEWCONTEXT

    初始化普通context handle。

    函数原型：

    DBE_XMLGEN.NEWCONTEXT(QUERYSTRING IN VARCHAR2) RETURNS DBE_XMLGEN.CTXHANDLE;
DBE_XMLGEN.NEWCONTEXT(QUERYSTRING IN SYS_REFCURSOR) RETURNS DBE_XMLGEN.CTXHANDLE;

    参数说明：

    表5 DBE_XMLGEN.NEWCONTEXT接口参数说明

    参数

    描述

    QUERYSTRING

    用于生成XML的查询SQL语句或SYS_REFCURSOR。

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- 初始化普通context handle。
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
BEGIN
 qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * FROM DEPARTMENT ORDER BY DEPARTMENT_ID');
 result:=DBE_XMLGEN.GETXML(qryctx);
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
 DBE_OUTPUT.PUT_LINE(result);
END;
/

<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

-- 初始化普通context handle。
DECLARE
    lr SYS_REFCURSOR;
    qryctx DBE_XMLGEN.CTXHANDLE;
    result XMLTYPE;
BEGIN
    OPEN lr FOR SELECT department_id, department_name FROM DEPARTMENT ORDER BY DEPARTMENT_ID;
    qryctx:=DBE_XMLGEN.NEWCONTEXT(lr);
 result:=DBE_XMLGEN.GETXMLTYPE(qryctx);
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
 DBE_OUTPUT.PUT_LINE(result.getclobval);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
</row>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
</row>
<row>
  <department_id>15</department_id>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
</row>
</rowset>
  • DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY

    初始化带有递归元素的context handle。

    数据格式要求为两列，第一列为numeric类型，第二列为xml或XMLTYPE类型。通常情况下由connect by语句生成，第一列指定生成level。

    生成的xml层级嵌套不允许超过5000万层。

    函数原型：

    DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY(QUERYSTRING IN VARCHAR2);

    参数说明：

    表6 DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY接口参数说明

    参数

    描述

    QUERYSTRING

    需要转换的XML字符串，VARCHAR2类型。

    示例：

    -- 预置数据。
CREATE TABLE tree_list(id NUMBER, name VARCHAR2(30), fid NUMBER);
INSERT INTO tree_list VALUES(1, 'a', NULL);
INSERT INTO tree_list VALUES(6, 'a-1-2', 2);
INSERT INTO tree_list VALUES(2, 'a-1', 1);
INSERT INTO tree_list VALUES(3, 'a-2', 1);
INSERT INTO tree_list VALUES(4, 'a-3', 1);
INSERT INTO tree_list VALUES(5, 'a-1-1', 2);
INSERT INTO tree_list VALUES(7, 'a-2-1', 3);
INSERT INTO tree_list VALUES(8, 'a-2-2', 3);
INSERT INTO tree_list VALUES(9, 'a-3-1', 4);
INSERT INTO tree_list VALUES(10, 'a-3-2', 4);
INSERT INTO tree_list VALUES(11, 'a-3-2-1', 10);
INSERT INTO tree_list VALUES(12, 'a-3-2-1-1', 11);
INSERT INTO tree_list VALUES(13, 'a-3-2-1-1-1', 12);
INSERT INTO tree_list VALUES(14, 'a-3-2-1-1-1-1', 13);
INSERT INTO tree_list VALUES(15, 'a-3-2-1-1-1-1-1', 14);
INSERT INTO tree_list VALUES(16, NULL, 14);
INSERT INTO tree_list VALUES(17, '<?q>', 14);
-- 递归xml生成。
DECLARE
qryctx DBE_XMLGEN.CTXHANDLE;
result CLOB;
BEGIN
 qryctx := DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY('SELECT level, xmlelement("children", xmlelement("node_name", name)) ss from tree_list start with id=1 connect by prior id=fid');
 DBE_XMLGEN.USENULLATTRIBUTEINDICATOR(qryctx, true);
 result:=DBE_XMLGEN.GETXML(qryctx);
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
 DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0" encoding="utf-8"?>
<children>
  <node_name>a</node_name>
  <children>
    <node_name>a-3</node_name>
    <children>
      <node_name>a-3-2</node_name>
      <children>
        <node_name>a-3-2-1</node_name>
        <children>
          <node_name>a-3-2-1-1</node_name>
          <children>
            <node_name>a-3-2-1-1-1</node_name>
            <children>
              <node_name>a-3-2-1-1-1-1</node_name>
              <children>
                <node_name>&lt;?q&gt;</node_name>
              </children>
              <children>
                <node_name/>
              </children>
              <children>
                <node_name>a-3-2-1-1-1-1-1</node_name>
              </children>
            </children>
          </children>
        </children>
      </children>
    </children>
    <children>
      <node_name>a-3-1</node_name>
    </children>
  </children>
  <children>
    <node_name>a-2</node_name>
    <children>
      <node_name>a-2-2</node_name>
    </children>
    <children>
      <node_name>a-2-1</node_name>
    </children>
  </children>
  <children>
    <node_name>a-1</node_name>
    <children>
      <node_name>a-1-1</node_name>
    </children>
    <children>
      <node_name>a-1-2</node_name>
    </children>
  </children>
</children>
  • DBE_XMLGEN.SETCONVERTSPECIALCHARS

    设置输出的xml是否需要xml编码。取消xml编码可能会存在xml注入问题，如果出于性能考虑且可以保证xml是安全的情况下可以不进行xml编码。

    函数原型：

    DBE_XMLGEN.SETCONVERTSPECIALCHARS(CTX IN DBE_XMLGEN.CTXHANDLE, CONV IN BOOLEAN);

    参数说明：

    表7 DBE_XMLGEN.SETCONVERTSPECIALCHARS接口参数说明

    参数

    描述

    CTX

    context handle。

    CONV

    是否需要对输出的xml进行编码。

    • true：编码。
    • false：不编码。

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- xml编码。
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
BEGIN
 qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=14');
 DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, true);
 result:=DBE_XMLGEN.GETXML(qryctx);
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
 DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>
-- 不进行编码。
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
BEGIN
	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=14');
	DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, false);
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>14</department_id>
  <department_name>aaa</row><a>asd</a><row></department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>
-- 对其余非xml的特殊字符不进行编码。
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
BEGIN
	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=16');
	DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, true);
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>
  • DBE_XMLGEN.SETNULLHANDLING

    设置xml中null值如何展示。

    函数原型：

    DBE_XMLGEN.SETNULLHANDLING(CTX IN DBE_XMLGEN.CTXHANDLE, FLAG IN NUMBER := 0);

    参数说明：

    表8 DBE_XMLGEN.SETNULLHANDLING接口参数说明

    参数

    描述

    CTX

    context handle。

    FLAG

    NULL值展示格式。

    0：不展示元素。

    1：元素上添加 xsi:nil="true" 属性。

    2：展示自闭合元素

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);

-- 不用nullhandling的默认值。
DECLARE
    result CLOB;
    qryctx DBE_XMLGEN.CTXHANDLE;
BEGIN
	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
</rowset>
-- nullhandling为0
DECLARE
    result CLOB;
    qryctx DBE_XMLGEN.CTXHANDLE;
BEGIN
	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
	DBE_XMLGEN.SETNULLHANDLING(qryctx, 0);
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
</rowset>
-- nullhandling 1
DECLARE
    result CLOB;
    qryctx DBE_XMLGEN.CTXHANDLE;
BEGIN
	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
	DBE_XMLGEN.SETNULLHANDLING(qryctx, 1);
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
<?xml version="1.0"?>
<rowset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
  <department_id>15</department_id>
  <department_name xsi:nil="true"/>
  <manager>500</manager>
  <location>1600</location>
</row>
</rowset>
-- nullhandling 2
DECLARE
    result CLOB;
    qryctx DBE_XMLGEN.CTXHANDLE;
BEGIN
	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
	DBE_XMLGEN.SETNULLHANDLING(qryctx, 2);
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>15</department_id>
  <department_name/>
  <manager>500</manager>
  <location>1600</location>
</row>
</rowset>
  • DBE_XMLGEN.SETROWSETTAG

    设置xml根节点名称。

    函数原型：

    DBE_XMLGEN.SETROWSETTAG(CTX IN DBE_XMLGEN.CTXHANDLE, ROWSETTAGNAME IN VARCHAR2);

    参数说明：

    表9 DBE_XMLGEN.SETROWSETTAG接口参数说明

    参数

    描述

    CTX

    context handle。

    ROWSETTAGNAME

    xml根节点名称。

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- 设置根节点名称为asd。
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
BEGIN
	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * FROM DEPARTMENT ORDER BY DEPARTMENT_ID');
	DBE_XMLGEN.SETROWSETTAG(qryctx, 'asd');
	DBE_XMLGEN.SETROWTAG(qryctx, 'qwe');
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0"?>
<asd>
<qwe>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</qwe>
<qwe>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</qwe>
<qwe>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</qwe>
</asd>
  • DBE_XMLGEN.SETROWTAG

    设置xml中每一行数据的tag名。

    函数原型：

    DBE_XMLGEN.SETROWTAG(CTX IN DBE_XMLGEN.CTXHANDLE, ROWTAGNAME IN VARCHAR2);

    参数说明：

    表10 DBE_XMLGEN.SETROWTAG接口参数说明

    参数

    描述

    CTX

    context handle。

    ROWTAGNAME

    每一行数据的tag名。

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- 设置每一行数据的tag名为qwe。
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
BEGIN
	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * FROM DEPARTMENT ORDER BY DEPARTMENT_ID');
	DBE_XMLGEN.SETROWSETTAG(qryctx, 'asd');
	DBE_XMLGEN.SETROWTAG(qryctx, 'qwe');
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0"?>
<asd>
<qwe>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</qwe>
<qwe>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</qwe>
<qwe>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</qwe>
<qwe>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</qwe>
</asd>
  • DBE_XMLGEN.USENULLATTRIBUTEINDICATOR

    对xml中的null值所在的元素添加xsi:nil="true"属性。

    函数原型：

    DBE_XMLGEN.USENULLATTRIBUTEINDICATOR(CTX IN DBE_XMLGEN.CTXHANDLE, ATTRIND IN BOOLEAN);

    参数说明：

    表11 DBE_XMLGEN.USENULLATTRIBUTEINDICATOR接口参数说明

    参数

    描述

    CTX

    context handle。

    ATTRIND

    无意义。

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- 对null值添加 xsi:nil="true"属性。
DECLARE
    result CLOB;
    qryctx DBE_XMLGEN.CTXHANDLE;
BEGIN
	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=15');
	DBE_XMLGEN.USENULLATTRIBUTEINDICATOR(qryctx, true);
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
<?xml version="1.0"?>
<rowset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
  <department_id>15</department_id>
  <department_name xsi:nil="true"/>
  <manager>500</manager>
  <location>1600</location>
</row>
</rowset>
  • DBE_XMLGEN.USEITEMTAGSFORCOLL

    对数组类型变量所在的元素中添加'_item'后缀。

    函数原型：

    DBE_XMLGEN.USEITEMTAGSFORCOLL(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明：

    表12 DBE_XMLGEN.USEITEMTAGSFORCOLL接口参数说明

    参数

    描述

    CTX

    context handle。

    示例：

    -- 预置数据。
CREATE TABLE test_for_array(id INT[]);
INSERT INTO test_for_array VALUES(ARRAY[1,2,3]);
SELECT DBE_XMLGEN.GETXML('SELECT * from test_for_array');
-- 数组类型添加'_item'后缀。
DECLARE
qryctx DBE_XMLGEN.CTXHANDLE;
result CLOB;
BEGIN
	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from test_for_array');
	DBE_XMLGEN.useItemTagsForColl(qryctx);
	result:=DBE_XMLGEN.GETXML(qryctx);
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
	DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <id>
    <int4_ITEM>1</int4_ITEM>
    <int4_ITEM>2</int4_ITEM>
    <int4_ITEM>3</int4_ITEM>
  </id>
</row>
</rowset>
  • DBE_XMLGEN.GETNUMROWSPROCESSED

    查看上一次getxml或者getxmltype返回的数据行数。

    函数原型：

    DBE_XMLGEN.GETNUMROWSPROCESSED(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明：

    表13 DBE_XMLGEN.GETNUMROWSPROCESSED接口参数说明

    参数

    描述

    CTX

    context handle。

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- getNumRowsProcessed
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
	result CLOB;
BEGIN
	qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
	-- 每一次查询返回的最大值。
	DBE_XMLGEN.SETMAXROWS(qryctx, 4);
	LOOP
        result := DBE_XMLGEN.GETXML(qryctx);
		-- 这一轮查询返回的数量。
		exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
		DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
		DBE_OUTPUT.PUT_LINE(result);
		DBE_OUTPUT.PUT_LINE('********************');
    END LOOP;
	DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
-------4----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
-------3----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
  • DBE_XMLGEN.SETMAXROWS

    设置getxml最大的返回行数。

    函数原型：

    DBE_XMLGEN.SETMAXROWS(CTX IN DBE_XMLGEN.CTXHANDLE, MAXROWS IN NUMBER);

    参数说明：

    表14 DBE_XMLGEN.SETMAXROWS接口参数说明

    参数

    描述

    CTX

    context handle。

    MAXROWS

    每一次getxml最大的返回行数。

    示例：

    -- 预置数据。
CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- getNumRowsProcessed
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
 result CLOB;
BEGIN
 qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
 -- 每一次查询返回的最大值。
 DBE_XMLGEN.SETMAXROWS(qryctx, 4);
 LOOP
        result := DBE_XMLGEN.GETXML(qryctx);
 -- 这一轮查询返回的数量。
  exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
  DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
  DBE_OUTPUT.PUT_LINE(result);
  DBE_OUTPUT.PUT_LINE('********************');
    END LOOP;
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
-------4----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
-------3----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
  • DBE_XMLGEN.SETSKIPROWS

    设置跳过sql行数。

    函数原型：

    DBE_XMLGEN.SETSKIPROWS(CTX IN DBE_XMLGEN.CTXHANDLE, SKIPROWS IN NUMBER);

    参数说明：

    表15 DBE_XMLGEN.SETSKIPROWS接口参数说明

    参数

    描述

    CTX

    context handle。

    SKIPROWS

    跳过SQL的头部行数。

    示例：

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- setskiprows跳过5行。
declare
    result CLOB;
    qryctx DBE_XMLGEN.CTXHANDLE;
BEGIN
 qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
 DBE_XMLGEN.SETSKIPROWS(qryctx, 5);
 result:=DBE_XMLGEN.GETXML(qryctx);
 DBE_OUTPUT.PUT_LINE(result);
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
  • DBE_XMLGEN.RESTARTQUERY

    重启sql。

    函数原型：

    DBE_XMLGEN.RESTARTQUERY(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明：

    表16 DBE_XMLGEN.RESTARTQUERY接口参数说明

    参数

    描述

    CTX

    context handle。

    示例：

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);

DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
 result CLOB;
BEGIN
 qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
 -- 每一次查询返回的最大值。
 DBE_XMLGEN.SETMAXROWS(qryctx, 4);
 LOOP
        result := DBE_XMLGEN.GETXML(qryctx);
        -- 这一轮查询返回的数量。
        exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
        DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
        DBE_OUTPUT.PUT_LINE(result);
        DBE_OUTPUT.PUT_LINE('********************');
    END LOOP;
    -- 重启查询。
    DBE_XMLGEN.RESTARTQUERY(qryctx);
    result := DBE_XMLGEN.GETXML(qryctx);
    DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
    DBE_OUTPUT.PUT_LINE(result);
    DBE_OUTPUT.PUT_LINE('********************');
    DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
-------4----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
-------3----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
-------4----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
  • DBE_XMLGEN.GETXMLTYPE

    返回XMLTYPE类型的xml文本。

    函数原型：

    DBE_XMLGEN.GETXMLTYPE(SQLQUERY IN VARCHAR2, DTDORSCHEMA IN NUMBER := 0) RETURNS XMLTYPE;
DBE_XMLGEN.GETXMLTYPE(CTX IN DBE_XMLGEN.CTXHANDLE, DTDORSCHEMA IN NUMBER := 0) RETURNS XMLTYPE;

    参数说明：

    表17 DBE_XMLGEN.GETXMLTYP接口参数说明

    参数

    描述

    SQLQUERY

    需要转换成XML的查询SQL。

    DTDORSCHEMA

    无意义

    CTX

    context handle。

    示例：

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
DECLARE
    lr SYS_REFCURSOR;
    qryctx DBE_XMLGEN.CTXHANDLE;
    result XMLTYPE;
BEGIN
    OPEN lr FOR SELECT department_id, department_name FROM DEPARTMENT ORDER BY DEPARTMENT_ID;
    qryctx:=DBE_XMLGEN.NEWCONTEXT(lr);
 result:=DBE_XMLGEN.GETXMLTYPE(qryctx);
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
        -- 返回的是xmltype类型所以需要用getclobval转换才能被put_line输出。
 DBE_OUTPUT.PUT_LINE(result.getclobval);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
</row>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
</row>
<row>
  <department_id>15</department_id>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
</row>
</rowset>
  • DBE_XMLGEN.GETXML

    返回CLOB类型的xml文本。

    函数原型：

    DBE_XMLGEN.GETXML(SQLQUERY IN VARCHAR2, DTDORSCHEMA IN NUMBER := 0) RETURNS CLOB;
DBE_XMLGEN.GETXML(CTX IN DBE_XMLGEN.CTXHANDLE, DTDORSCHEMA IN NUMBER := 0) RETURNS CLOB;
DBE_XMLGEN.GETXML(CTX IN DBE_XMLGEN.CTXHANDLE, TMPCLOB INOUT CLOB, DTDORSCHEMA IN NUMBER := 0);

    参数说明：

    表18 DBE_XMLGEN.GETXML接口参数说明

    参数

    描述

    SQLQUERY

    需要转换成XML的查询SQL。

    DTDORSCHEMA

    无意义

    CTX

    context handle。

    TMPCLOB

    用于保存输出的XML的CLOB变量。

    示例：

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
 result CLOB;
BEGIN
 qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department');
 -- 每一次查询返回的最大值。
 DBE_XMLGEN.SETMAXROWS(qryctx, 4);
 LOOP
        result := DBE_XMLGEN.GETXML(qryctx);
        -- 这一轮查询返回的数量。
        exit when DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx) = 0;
        DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
        DBE_OUTPUT.PUT_LINE(result);
        DBE_OUTPUT.PUT_LINE('********************');
    END LOOP;
    DBE_XMLGEN.RESTARTQUERY(qryctx);
    result := DBE_XMLGEN.GETXML(qryctx);
    DBE_OUTPUT.PUT_LINE('-------'||DBE_XMLGEN.GETNUMROWSPROCESSED(qryctx)||'----------');
    DBE_OUTPUT.PUT_LINE(result);
    DBE_OUTPUT.PUT_LINE('********************');
    DBE_XMLGEN.CLOSECONTEXT(qryctx);
END;
/
-------4----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
-------3----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>14</department_id>
  <department_name>aaa&lt;/row&gt;&lt;a&gt;asd&lt;/a&gt;&lt;row&gt;</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
<row>
  <department_id>15</department_id>
  <manager>500</manager>
  <location>1600</location>
</row>
<row>
  <department_id>16</department_id>
  <department_name>!@#$%^&amp;*()+-=&lt;&gt;/\"a3_啊</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
-------4----------
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>10</department_id>
  <department_name>administrator</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>11</department_id>
  <department_name>aaa</department_name>
  <manager>200</manager>
  <location>1700</location>
</row>
<row>
  <department_id>12</department_id>
  <department_name>bbb</department_name>
  <manager>300</manager>
  <location>1600</location>
</row>
<row>
  <department_id>13</department_id>
  <department_name>ccc</department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>

********************
  • DBE_XMLGEN.CLOSECONTEXT

    关闭context handle。

    函数原型：

    DBE_XMLGEN.CLOSECONTEXT(CTX IN DBE_XMLGEN.CTXHANDLE);

    参数说明：

    表19 DBE_XMLGEN.CLOSECONTEXT接口参数说明

    参数

    描述

    CTX

    context handle。

    示例：

    CREATE TABLE IF NOT EXISTS department(department_id NUMBER, department_name VARCHAR2(30), manager NUMBER, location NUMBER);
INSERT INTO department VALUES(10, 'administrator', 200, 1700);
INSERT INTO department VALUES(11, 'aaa', 200, 1700);
INSERT INTO department VALUES(12, 'bbb', 300, 1600);
INSERT INTO department VALUES(13, 'ccc', 400, 1600);
INSERT INTO department VALUES(14, 'aaa</row><a>asd</a><row>', 400, 1600);
INSERT INTO department VALUES(15, NULL, 500,1600);
INSERT INTO department VALUES(16, '!@#$%^&*()+-=<>/\"a3_啊', 400, 1600);
-- 关闭context。
DECLARE
    qryctx DBE_XMLGEN.CTXHANDLE;
    result CLOB;
BEGIN
 qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT * from department where department_id=14');
 DBE_XMLGEN.SETCONVERTSPECIALCHARS(qryctx, false);
 result:=DBE_XMLGEN.GETXML(qryctx);
 DBE_XMLGEN.CLOSECONTEXT(qryctx);
 DBE_OUTPUT.PUT_LINE(result);
END;
/
<?xml version="1.0"?>
<rowset>
<row>
  <department_id>14</department_id>
  <department_name>aaa</row><a>asd</a><row></department_name>
  <manager>400</manager>
  <location>1600</location>
</row>
</rowset>