Help Center/
GaussDB/
Developer Guide(Centralized_8.x)/
Stored Procedure/
Advanced Packages/
Secondary Encapsulation APIs (Recommended)/
DBE_XMLGEN
Updated on 2024-08-20 GMT+08:00
DBE_XMLGEN
API Description
The DBE_XMLGEN system package converts the result of an SQL query into a standard XML format and returns the result. For details about all supported APIs, see Table 2 DBE_XMLGEN.
|
Type |
Description |
|---|---|
|
DBE_XMLGEN.CTXHANDLE |
Data type used to store the XML output status. |
- A maximum of 65,535 context handles can exist in a session. Closing the context handle does not reclaim the number.
- The case of the table column and type in the output XML file is the same as that of the table field and type created by the user. If you need to write a large field and type name, use double quotation marks ("") to enclose the field and type name.
- During NEWCONTEXTFROMHIERARCHY initialization, the SETNULLHANDLING, USENULLATTRIBUTEINDICATOR, and SETCONVERTSPECIALCHARS methods are used to set this parameter, but the setting does not take effect.
|
API |
Description |
|---|---|
|
Encodes or decodes the input character string in XML format. |
|
|
Initializes the common context handle. |
|
|
Initializes the context handle with recursive elements. |
|
|
Specifies whether the output XML file needs to be encoded. |
|
|
Sets how to display the null value in the XML file. |
|
|
Sets the name of the XML root node. |
|
|
Sets the tag name of each row of data in the XML file. |
|
|
Adds the xsi:nil="true" attribute to the element where the null value is located in the XML file. |
|
|
Adds the suffix '_item' to the element where the variable of the array type is located. |
|
|
Views the number of data rows returned by getxml or getxmltype last time. |
|
|
Sets the maximum number of rows returned by getxml. |
|
|
Sets the number of SQL rows to be skipped. |
|
|
Restarts the SQL. |
|
|
Returns the XML text of the XMLTYPE type. |
|
|
Returns the XML text of the CLOB type. |
|
|
Disables the context handle. |
- DBE_XMLGEN.CONVERT
Encodes or decodes the input character string in XML format.
The conversion is performed according to the following rules.
Table 3 XML encoding rules Original Value
Code Value
&
&
<
<
>
>
"
"
'
'
Function prototype:
DBE_XMLGEN.CONVERT(XMLSTR IN VARCHAR2, FLAG IN NUMBER := 0) RETURNS VARCHAR2; DBE_XMLGEN.CONVERT(XMLCLOB IN CLOB, FLAG IN NUMBER := 0) RETURNS CLOB;
Parameter description
Table 4 DBE_XMLGEN.CONVERT parameters Parameter
Description
XMLSTR
XML character string to be converted. The value is of the VARCHAR2 type.
XMLCLOB
XML character string to be converted. The value is of the CLOB type.
FLAG
Transcodes or decodes character strings.
0: encoding.
1: decoding.
Example:
-- XML decoding SELECT DBE_XMLGEN.CONVERT('<foo/>', 1); convert --------- <foo/> (1 row) -- XML encoding SELECT DBE_XMLGEN.CONVERT('<foo><qwe</foo>', 0); convert -------------------------------- <foo><qwe</foo> (1 row) - DBE_XMLGEN.NEWCONTEXT
Initializes the common context handle.
Function prototype:
DBE_XMLGEN.NEWCONTEXT(QUERYSTRING IN VARCHAR2) RETURNS DBE_XMLGEN.CTXHANDLE; DBE_XMLGEN.NEWCONTEXT(QUERYSTRING IN SYS_REFCURSOR) RETURNS DBE_XMLGEN.CTXHANDLE;
Parameter description
Table 5 DBE_XMLGEN.NEWCONTEXT parameters Parameter
Description
QUERYSTRING
Queries SQL statement or SYS_REFCURSOR used to generate XML files.
Example:
-- Preset data. 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_a', 400, 1600); -- Initialize the common 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</row><a>asd</a><row></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>!@#$%^&*()+-=<>/\"a3_a</department_name> <manager>400</manager> <location>1600</location> </row> </rowset> -- Initialize the common 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</row><a>asd</a><row></department_name> </row> <row> <department_id>15</department_id> </row> <row> <department_id>16</department_id> <department_name>!@#$%^&*()+-=<>/\"a3_a</department_name> </row> </rowset> - DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY
Initializes the context handle with recursive elements.
The data format must contain two columns. The first column is of the numeric type, and the second column is of the XML or XMLTYPE type. Generally, the value is generated by the connect by statement. The first column specifies the generation level.
The number of nested layers in the generated XML file cannot exceed 50 million.
Function prototype:
DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY(QUERYSTRING IN VARCHAR2);
Parameter description
Table 6 DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY parameters Parameter
Description
QUERYSTRING
XML character string to be converted. The value is of the VARCHAR2 type.
Example:
-- Preset data. 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); -- Recursive XML generation. 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><?q></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
Specifies whether the output XML file needs to be encoded. If XML encoding is canceled, XML injection may occur. If the XML is secured and the performance is considered, XML encoding is not required.
Function prototype:
DBE_XMLGEN.SETCONVERTSPECIALCHARS(CTX IN DBE_XMLGEN.CTXHANDLE, CONV IN BOOLEAN);
Parameter description:
Table 7 DBE_XMLGEN.SETCONVERTSPECIALCHARS parameters Parameter
Description
CTX
Context handle.
CONV
Specifies whether to encode the output XML file.
- true: yes
- false: no
Example:
-- Preset data. 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_a', 400, 1600); -- XML encoding. 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</row><a>asd</a><row></department_name> <manager>400</manager> <location>1600</location> </row> </rowset> -- No encoding is performed. 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> -- Other non-XML special characters are not encoded. 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>!@#$%^&*()+-=<>/\"a3_a</department_name> <manager>400</manager> <location>1600</location> </row> </rowset> - DBE_XMLGEN.SETNULLHANDLING
Sets how to display the null value in the XML file.
Function prototype:
DBE_XMLGEN.SETNULLHANDLING(CTX IN DBE_XMLGEN.CTXHANDLE, FLAG IN NUMBER := 0);
Parameter description:
Table 8 DBE_XMLGEN.SETNULLHANDLING parameters Parameter
Description
CTX
Context handle.
FLAG
Null value display format.
0: The element is not displayed.
1: The xsi:nil="true" attribute is added to the element.
2: Self-closed elements are displayed.
Example:
-- Preset data. 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_a', 400, 1600); -- The default value of nullhandling is not used. 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 is 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
Sets the name of the XML root node.
Function prototype:
DBE_XMLGEN.SETROWSETTAG(CTX IN DBE_XMLGEN.CTXHANDLE, ROWSETTAGNAME IN VARCHAR2);
Parameter description:
Table 9 DBE_XMLGEN.SETROWSETTAG parameters Parameter
Description
CTX
Context handle.
ROWSETTAGNAME
Name of the XML root node.
Example:
-- Preset data. 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_a', 400, 1600); -- Set the root node name to 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</row><a>asd</a><row></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>!@#$%^&*()+-=<>/\"a3_a</department_name> <manager>400</manager> <location>1600</location> </qwe> </asd> - DBE_XMLGEN.SETROWTAG
Sets the tag name of each row of data in the XML file.
Function prototype:
DBE_XMLGEN.SETROWTAG(CTX IN DBE_XMLGEN.CTXHANDLE, ROWTAGNAME IN VARCHAR2);
Parameter description:
Table 10 DBE_XMLGEN.SETROWTAG parameters Parameter
Description
CTX
Context handle.
ROWTAGNAME
Tag name of each row of data.
Example:
-- Preset data. 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_a', 400, 1600); -- Set the tag name of each row of data to 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</row><a>asd</a><row></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>!@#$%^&*()+-=<>/\"a3_a</department_name> <manager>400</manager> <location>1600</location> </qwe> </asd> - DBE_XMLGEN.USENULLATTRIBUTEINDICATOR
Adds the xsi:nil="true" attribute to the element where the null value is located in the XML file.
Function prototype:
DBE_XMLGEN.USENULLATTRIBUTEINDICATOR(CTX IN DBE_XMLGEN.CTXHANDLE, ATTRIND IN BOOLEAN);
Parameter description:
Table 11 DBE_XMLGEN.USENULLATTRIBUTEINDICATOR parameters Parameter
Description
CTX
Context handle.
ATTRIND
None.
Example:
-- Preset data. 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_a', 400, 1600); -- Add the xsi:nil="true" attribute to the null value. 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
Adds the suffix '_item' to the element where the variable of the array type is located.
Function prototype:
DBE_XMLGEN.USEITEMTAGSFORCOLL(CTX IN DBE_XMLGEN.CTXHANDLE);
Parameter description:
Table 12 DBE_XMLGEN.USEITEMTAGSFORCOLL parameters Parameter
Description
CTX
Context handle.
Example:
-- Preset data. 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'); -- The suffix '_item' is added to the array type. 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
Views the number of data rows returned by getxml or getxmltype last time.
Function prototype:
DBE_XMLGEN.GETNUMROWSPROCESSED(CTX IN DBE_XMLGEN.CTXHANDLE);
Parameter description:
Table 13 DBE_XMLGEN.GETNUMROWSPROCESSED parameters Parameter
Description
CTX
Context handle.
Example:
-- Preset data. 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_a', 400, 1600); -- getNumRowsProcessed DECLARE qryctx DBE_XMLGEN.CTXHANDLE; result CLOB; BEGIN qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department'); -- Maximum value returned for each query. DBE_XMLGEN.SETMAXROWS(qryctx, 4); LOOP result := DBE_XMLGEN.GETXML(qryctx); -- Number of records returned in this round of query. 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</row><a>asd</a><row></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>!@#$%^&*()+-=<>/\"a3_a</department_name> <manager>400</manager> <location>1600</location> </row> </rowset> ******************** - DBE_XMLGEN.SETMAXROWS
Sets the maximum number of rows returned by getxml.
Function prototype:
DBE_XMLGEN.SETMAXROWS(CTX IN DBE_XMLGEN.CTXHANDLE, MAXROWS IN NUMBER);
Parameter description:
Table 14 DBE_XMLGEN.SETMAXROWS parameters Parameter
Description
CTX
Context handle.
MAXROWS
Maximum number of rows returned by each getxml operation.
Example:
-- Preset data. 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_a', 400, 1600); -- getNumRowsProcessed DECLARE qryctx DBE_XMLGEN.CTXHANDLE; result CLOB; BEGIN qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department'); -- Maximum value returned for each query. DBE_XMLGEN.SETMAXROWS(qryctx, 4); LOOP result := DBE_XMLGEN.GETXML(qryctx); -- Number of records returned in this round of query. 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</row><a>asd</a><row></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>!@#$%^&*()+-=<>/\"a3_a</department_name> <manager>400</manager> <location>1600</location> </row> </rowset> ******************** - DBE_XMLGEN.SETSKIPROWS
Sets the number of SQL rows to be skipped.
Function prototype:
DBE_XMLGEN.SETSKIPROWS(CTX IN DBE_XMLGEN.CTXHANDLE, SKIPROWS IN NUMBER);
Parameter description:
Table 15 DBE_XMLGEN.SETSKIPROWS parameters Parameter
Description
CTX
Context handle.
SKIPROWS
Number of SQL header rows that are skipped.
Example:
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_a', 400, 1600); -- Use setskiprows to skip five rows. 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>!@#$%^&*()+-=<>/\"a3_a</department_name> <manager>400</manager> <location>1600</location> </row> - DBE_XMLGEN.RESTARTQUERY
Function prototype:
DBE_XMLGEN.RESTARTQUERY(CTX IN DBE_XMLGEN.CTXHANDLE);
Parameter description:
Table 16 DBE_XMLGEN.RESTARTQUERY parameters Parameter
Description
CTX
Context handle.
Example:
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_a', 400, 1600); DECLARE qryctx DBE_XMLGEN.CTXHANDLE; result CLOB; BEGIN qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department'); -- Maximum value returned for each query. DBE_XMLGEN.SETMAXROWS(qryctx, 4); LOOP result := DBE_XMLGEN.GETXML(qryctx); -- Number of records returned in this round of query. 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; -- Retry the query. 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</row><a>asd</a><row></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>!@#$%^&*()+-=<>/\"a3_a</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
Returns the XML text of the XMLTYPE type.
Function prototype:
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;
Parameter description:
Table 17 DBE_XMLGEN.GETXMLTYP parameters Parameter
Description
SQLQUERY
Query SQL statements that need to be converted into XML files.
DTDORSCHEMA
None.
CTX
Context handle.
Example:
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_a', 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); -- The returned value is of the XMLTYPE type. Therefore, the value can be output by put_line only after being converted by getclobval. 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</row><a>asd</a><row></department_name> </row> <row> <department_id>15</department_id> </row> <row> <department_id>16</department_id> <department_name>!@#$%^&*()+-=<>/\"a3_a</department_name> </row> </rowset> - DBE_XMLGEN.GETXML
Returns the XML text of the CLOB type.
Function prototype:
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);
Parameter description:
Table 18 DBE_XMLGEN.GETXML parameters Parameter
Description
SQLQUERY
Query SQL statements that need to be converted into XML files.
DTDORSCHEMA
None.
CTX
Context handle.
TMPCLOB
CLOB variable for storing the output XML.
Example:
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_a', 400, 1600); DECLARE qryctx DBE_XMLGEN.CTXHANDLE; result CLOB; BEGIN qryctx:=DBE_XMLGEN.NEWCONTEXT('SELECT * from department'); -- Maximum value returned for each query. DBE_XMLGEN.SETMAXROWS(qryctx, 4); LOOP result := DBE_XMLGEN.GETXML(qryctx); -- Number of records returned in this round of query. 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</row><a>asd</a><row></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>!@#$%^&*()+-=<>/\"a3_a</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
Function prototype:
DBE_XMLGEN.CLOSECONTEXT(CTX IN DBE_XMLGEN.CTXHANDLE);
Parameter description:
Table 19 DBE_XMLGEN.CLOSECONTEXT parameters Parameter
Description
CTX
Context handle.
Example:
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_a', 400, 1600); -- Disable the 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>
Parent topic: Secondary Encapsulation APIs (Recommended)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot
