文档首页> 云数据库 GaussDB> 主备版_8.x版本> 存储过程> 高级包> 二次封装接口（推荐）> DBE_XMLGEN

更新时间：2024-05-31 GMT+08:00

DBE_XMLGEN

接口介绍

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

表1 DBE_XMLGEN数据类型
类型名称	描述
DBE_XMLGEN.CTXHANDLE	用于存储XML输出状态的数据类型。

在同一个session中context handle最多只允许存在65535个。关闭context handle并不会回收这个数量。
输出的xml中表字段、类型与用户创建的表字段与类型大小写一致，如果需要大写字段与类型名需要在创建时用双引号包裹，显示指定。
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编码规则
原始值	编码值
&	&
<	<
>	>
"	"
'	'

函数原型：

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>

父主题： 二次封装接口（推荐）

上一篇：prvt_ilm

下一篇：Retry管理

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！您可在我的云声建议查看反馈及问题处理状态。

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消

如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问

DBE_XMLGEN

接口介绍

相关文档

相关产品

意见反馈

文档内容是否对您有帮助？

7*24

备案

专业服务

退订

建议反馈

售前咨询热线