DBE_XMLGEN

Interface 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 interfaces, see Table 2 DBE_XMLGEN.

1. A maximum of 65,535 context handles can exist in a session. Closing the context handle does not reclaim the number.
2. The connect by statement is not supported in the distributed system. Therefore, the newcontextfromhierarchy function data cannot be constructed.
3. In a distributed system, the cursor cannot be moved reversely. Therefore, the restartquery function is unavailable.
4. The case of the table column and type in the output XML file is the same as that of the table column and type created by the user. If you need to write a large column and type name, use double quotation marks ("") to specify the column and type name.
5. During NEWCONTEXTFROMHIERARCHY initialization, the SETNULLHANDLING, USENULLATTRIBUTEINDICATOR, and SETCONVERTSPECIALCHARS methods are used to set this parameter, but the setting does not take effect.
6. If ORDER BY is not added for query, the query result is disordered, which affects the return result of the GETXML function.

• 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
  &	&
  <	&lt;
  >	&gt;
  "	&quot;
  '	&apos;

  The function prototype of DBE_XMLGEN.CONVERT is:

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

  Table 4 DBE_XMLGEN.CONVERT interface 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             
  --------------------------------
   &lt;foo&gt;&lt;qwe&lt;/foo&gt;
  (1 row)

• DBE_XMLGEN.NEWCONTEXT

  Initializes the common context handle.

  The function prototype of DBE_XMLGEN.NEWCONTEXT is:

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

  Table 5 DBE_XMLGEN.NEWCONTEXT interface parameters

  Parameter	Description
  QUERYSTRING	Queries SQL statement or SYS_REFCURSOR used to generate XML files.

• 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 distributed system does not support the connect by statement. Therefore, this function case cannot be constructed.

  

  The number of nested layers in the generated XML file cannot exceed 50 million.

  The function prototype of DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY is:

  DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY(QUERYSTRING IN VARCHAR2);

  Table 6 DBE_XMLGEN.NEWCONTEXTFROMHIERARCHY interface parameters

  Parameter	Description
  QUERYSTRING	XML character string to be converted. The value is of the VARCHAR2 type.

• DBE_XMLGEN.SETCONVERTSPECIALCHARS

  Specifies whether the output XML 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.

  The function prototype of DBE_XMLGEN.SETCONVERTSPECIALCHARS is:

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

  Table 7 DBE_XMLGEN.SETCONVERTSPECIALCHARS interface parameters

  Parameter	Description
  CTX	Context handle.
  CONV	Specifies whether to encode the output XML file. true: Perform XML encoding. false: Do not perform XML encoding.

• DBE_XMLGEN.SETNULLHANDLING

  Sets how to display the null value in the XML file.

  The function prototype of DBE_XMLGEN.SETNULLHANDLING is:

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

  Table 8 DBE_XMLGEN.SETNULLHANDLING interface 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.

• DBE_XMLGEN.SETROWSETTAG

  Sets the name of the XML root node.

  The function prototype of DBE_XMLGEN.SETROWSETTAG is:

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

  Table 9 DBE_XMLGEN.SETROWSETTAG interface parameters

  Parameter	Description
  CTX	Context handle.
  ROWSETTAGNAME	Name of the XML root node.

• DBE_XMLGEN.SETROWTAG

  Sets the tag name of each row of data in the XML file.

  The function prototype of DBE_XMLGEN.SETROWTAG is:

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

  Table 10 DBE_XMLGEN.SETROWTAG interface parameters

  Parameter	Description
  CTX	Context handle.
  ROWTAGNAME	Tag name of each row of data.

• DBE_XMLGEN.USENULLATTRIBUTEINDICATOR

  Adds the xsi:nil="true" attribute to the element where the null value is located in the XML file.

  The function prototype of DBE_XMLGEN.USENULLATTRIBUTEINDICATOR is:

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

  Table 11 DBE_XMLGEN.USENULLATTRIBUTEINDICATOR interface parameters

  Parameter	Description
  CTX	Context handle.
  ATTRIND	None.

• DBE_XMLGEN.USEITEMTAGSFORCOLL

  Adds the suffix _item to the element where the variable of the array type is located.

  The function prototype of DBE_XMLGEN.USEITEMTAGSFORCOLL is:

  DBE_XMLGEN.USEITEMTAGSFORCOLL(CTX IN DBE_XMLGEN.CTXHANDLE);

  Table 12 DBE_XMLGEN.USEITEMTAGSFORCOLL interface parameters

  Parameter	Description
  CTX	Context handle.

  Example:

  -- Preset data.
  CREATE TABLE test_for_array(idd number, id INT[]);
  INSERT INTO test_for_array VALUES(1, ARRAY[1,2,3]);
  SELECT DBE_XMLGEN.GETXML('SELECT * from test_for_array');
  -- Add the suffix _item to the array type.
  DECLARE
  qryctx DBE_XMLGEN.CTXHANDLE;
  result CLOB;
  BEGIN
  	qryctx := DBE_XMLGEN.NEWCONTEXT('SELECT id 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.

  The function prototype of DBE_XMLGEN.GETNUMROWSPROCESSED is:

  DBE_XMLGEN.GETNUMROWSPROCESSED(CTX IN DBE_XMLGEN.CTXHANDLE);

  Table 13 DBE_XMLGEN.GETNUMROWSPROCESSED interface parameters

  Parameter	Description
  CTX	Context handle.

• DBE_XMLGEN.SETMAXROWS

  Sets maximum number of rows returned by each GETXML operation.

  The function prototype of DBE_XMLGEN.SETMAXROWS is:

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

  Table 14 DBE_XMLGEN.SETMAXROWS interface parameters

  Parameter	Description
  CTX	Context handle.
  MAXROWS	Maximum number of rows returned by each GETXML operation.

• DBE_XMLGEN.SETSKIPROWS

  Sets the number of SQL rows to be skipped.

  The function prototype of DBE_XMLGEN.SETSKIPROWS is:

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

  Table 15 DBE_XMLGEN.SETSKIPROWS interface parameters

  Parameter	Description
  CTX	Context handle.
  SKIPROWS	Number of SQL header rows that are skipped.

• DBE_XMLGEN.RESTARTQUERY

  Restarts the SQL. This interface is not supported in distributed mode.

  The function prototype of DBE_XMLGEN.RESTARTQUERY is:

  DBE_XMLGEN.RESTARTQUERY(CTX IN DBE_XMLGEN.CTXHANDLE);

  Table 16 DBE_XMLGEN.RESTARTQUERY interface parameters

  Parameter	Description
  CTX	Context handle.

• DBE_XMLGEN.GETXMLTYPE

  Returns the XML text of the XMLTYPE type.

  The function prototype of DBE_XMLGEN.GETXMLTYPE is:

  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;

  Table 17 DBE_XMLGEN.GETXMLTYP interface parameters

  Parameter	Description
  SQLQUERY	Query SQL statements that need to be converted into XML files.
  DTDORSCHEMA	None.
  CTX	Context handle.

• DBE_XMLGEN.GETXML

  Returns the XML text of the CLOB type.

  The function prototype of DBE_XMLGEN.GETXML is:

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

  Table 18 DBE_XMLGEN.GETXML interface 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.

• DBE_XMLGEN.CLOSECONTEXT

  Disables the context handle.

  The function prototype of DBE_XMLGEN.CLOSECONTEXT is:

  DBE_XMLGEN.CLOSECONTEXT(CTX IN DBE_XMLGEN.CTXHANDLE);

  Table 19 DBE_XMLGEN.CLOSECONTEXT interface 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>