Perl Cd Bookshelf [Electronic resources] نسخه متنی

Generating XML from SQL Data withDBMXMLGEN

DBMS_XMLGEN is a PL/SQL package supplied in Oracle Database 10g to generate XML in CLOBs or XMLTypes. It is a C-based implementation, which provides much better performance than the Java-based DBMS_XMLQUERY package in the Oracle8i and Oracle9i database. While DBMS_XMLGEN provides similar functionality to the SQL/XML functions, it is most useful when you need to

Utilize the “fetching” interfaces in DBMS_XMLGEN for pagination

Perform PL/SQL processing during the XML document generation

DBMS_XMLGEN is not limited to PL/SQL statements. You can also call its functions directly from any SQL statement using DBMS_XMLGEN.GETXML() or DBMS_XMGEN.GETXMLTYPE(). In many cases, you will find that this is easier than using the SQL/XML functions, as the input is simply a SQL statement. The following sections providing some typical examples and describe how you can use this package.

Canonical Mapping

Unlike the SQL/XML functions, DBMS_XMLGEN uses a canonical mapping to map the SQL data to XML. The format is exactly the same format used by the DBMS_XMLQUERY PL/SQL package or the XML SQL Utility (XSU) Java packages. The following example generates an XML document containing a set of employee records:

SQL> SET AUTOPRINT ON
SQL> SET LONG 100000
SQL> VAR result CLOB
SQL> DECLARE
qryCtx DBMS_XMLGEN.CTXHANDLE;
BEGIN
qryCtx :=
DBMS_XMLGEN.NEWCONTEXT('SELECT employee_id AS "@id",
first_name AS "FirstName",
last_name AS "LastName",
email AS "Email",
phone_number AS "Phone"
FROM employees
WHERE salary > 20000');
DBMS_XMLGEN.SETROWSETTAG(qryCtx,'EMPLOYEES');
DBMS_XMLGEN.SETROWTAG(qryCtx,'EMPLOYEE');
:result := DBMS_XMLGEN.GETXML(qryCtx);
DBMS_XMLGEN.CLOSECONTEXT(qryCtx);
END;

The result is

<?xml version="1.0"?>
<EMPLOYEES>
<EMPLOYEE id = "100">
<FirstName>Steven</FirstName>
<LastName>King</LastName>
<Email>SKING</Email>
<Phone>515.123.4567</Phone>
</EMPLOYEE>
</EMPLOYEES>

The canonical mapping uses the <ROW> element to enclose each row of data returned by the SQL query. The <ROW> elements are then included by the <ROWSET> element to create the XML document. In the example, the <ROWSET> or <ROW> element names are changed to EMPLOYEES and EMPLOYEE respectively by calling the DBMS_XMLGEN.SETROWSETTAG() and DBMS_XMLGEN.SETROWTAG() procedures.

By default, the child elements of <ROW> use the table column names as their element names. There is no PL/SQL procedure provided in the DBMS_XMLGEN package to customize the names of these elements, but, you can modify them via the AS clause in the SQL query. In the example, the LastName is set as the alias for the last_name column, and the employee_id column uses the alias @id. The difference between aliases with or without @ in the aliases, is that the column with an alias with @ is mapped to an XML attribute instead of an XML element.

Print Formatting

Using DBMS_XMLGEN, you can set the output formats using the following procedures:

 PROCEDURE SETINDENTATIONWIDTH(ctx IN CTXHANDLE, width IN NUMBER);
PROCEDURE SETPRETTYPRINTING(ctx IN CTXHANDLE, pp IN BOOLEAN);

By default, the output is in “pretty” print format with all the whitespaces, indentations and linefeeds between the XML elements, and you can set the number of whitespace characters used for indentation by calling DBMS_XMLGEN.SETINDENTATIONWIDTH(). By setting the second parameter of DBMS_XMLGEN.SETPRETTYPRINTING() to be FALSE, you can to get the XML documents generated in “compact” format with all the whitespaces between the XML element tags stripped out, as shown in the following example:

DECLARE
qryCtx DBMS_XMLGEN.CTXHANDLE;
BEGIN
qryCtx :=
DBMS_XMLGEN.NEWCONTEXT('SELECT employee_id AS "@id",
first_name as "FirstName",
last_name as "LastName",
email as "Email",
phone_number as "Phone"
FROM employees
WHERE salary > 20000');
DBMS_XMLGEN.SETPRETTYPRINTING(qryCtx, FALSE);
:result := DBMS_XMLGEN.GETXML(qryCtx);
DBMS_XMLGEN.CLOSECONTEXT(qryCtx);
END;

The result is

<?xml version="1.0"?>
<ROWSET><ROW><EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME><LAST_NAME>King</LAST_NAME>
<EMAIL>SKING</EMAIL><PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
<HIRE_DATE>17-JUN-87</HIRE_DATE><JOB_ID>AD_PRES</JOB_ID>
<SALARY>24000</SALARY><DEPARTMENT_ID>90</DEPARTMENT_ID>
</ROW></ROWSET>

Though this output is not the pretty format that you can easily read, an XML parser considers the data to be equivalent.

Data Fetching

When a large amount of data is returned by the SQL query, it is not efficient to generate a single huge XML document containing all the data. This will result in memory issues when performing DOM-based processing or transmitting the document to another user. The data “fetching” feature in DBMS_XMLGEN allows you to generate a set of small XML documents using the following procedures:

FUNCTION GETNUMROWSPROCESSED(ctx IN CTXHANDLE) RETURN NUMBER;
PROCEDURE SETMAXROWS(ctx IN CTXHANDLE, maxRows IN NUMBER);

Here is an example using these functions in a PL/SQL FOR...LOOP:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
qryCtx DBMS_XMLGEN.CTXHANDLE;
res BOOLEAN;
result XMLType;
i NUMBER :=0;
BEGIN
-- Create the query context
qryCtx := DBMS_XMLGEN.NEWCONTEXT(
'SELECT * FROM employees ORDER BY employee_id');
DBMS_XMLGEN.SETMAXROWS(qryCtx,10);
LOOP
result:= DBMS_XMLGEN.GETXMLTYPE(qryCtx);
EXIT WHEN DBMS_XMLGEN.GETNUMROWSPROCESSED(qryCtx) =0;
i :=i+1;
-- Create XML DB Resources
res := DBMS_XDB.CREATERESOURCE('/public/emp'||i||'.xml',result);
IF res = FALSE THEN
DBMS_OUTPUT.PUT_LINE('Error creating XML DB resource');
ELSE
DBMS_OUTPUT.PUT_LINE('/public/emp'||i||'.xml'||' is created');
END IF;
END LOOP;
COMMIT;
END;

The SQL query gets all the employees in order of their employee_id. By setting Max Rows to be 10, each XML document contains no greater than ten records. The DBMS_XMLGEN.GETNUMROWSPROCESSED() function keeps track of the number of rows that get decremented by 10 from the previous DBMS_XMLGEN.GETXML() calls. Once the loops have completed, the DBMS_XMLGEN.GETNUMROWSPROCESSED() returns zero, and the procedure exits.

During the creation of the outputs, DBMS_XMLGEN.GETXMLTYPE() is used to return the query result in XMLType. This XMLType is then used by DBMS_XDB.CREATERESOURCE() to create a resource file in the XML DB Repository. After the resource files are created, you can access the XML documents either through the WebDAV/HTTP or the FTP interfaces. For example, when you type the following HTTP address in Internet Explorer:

http://localhost:8080/public

A window pops up asking for login information. You can log in using any database username and its associated password. However, because the resource is created as a private resource by default, you need to log in as HR user to access the generated files from HR user, as shown in Figure 10-1.

Figure 10-1: HTTP access of XDB (XML DB) repository

From these interfaces, you can easily download the files from the database server to other client systems.

Using REF Cursor

In addition to creating the query context from SQL queries, DBMS_XMLGEN.NEWCONTEXT() allows you to create the query context based on REF cursors. This is done by specifying a SYS_REFCURSOR as the initialization parameter, as shown in the following example:

SQL> SET AUTOPRINT ON
SQL> VAR result CLOB;
SQL> DECLARE
salary NUMBER :=20000;
refcur SYS_REFCURSOR;
qryCtx DBMS_XMLGEN.CTXHANDLE;
BEGIN
OPEN refcur FOR 
'SELECT * FROM employees WHERE salary> :1' USING salary;
qryCtx := DBMS_XMLGEN.NEWCONTEXT(refcur);
:result := DBMS_XMLGEN.GETXML(qryCtx,DBMS_XMLGEN.NONE);
DBMS_XMLGEN.CLOSECONTEXT(qryCtx);
END;

This is useful when you need to create XML from a result set returned by another PL/SQL process.

Using Bind Variables

Whenever you use a SQL query repeatedly by simply updating several variables, you should think about using bind variables, because they eliminate the need to reparse the SQL statement, thus saving processing time. In the following example, we open a SYS_REFCURSOR for the department_id in the department table and use it to find all the employees within each department. When creating the loop for each department_id, DBMS_XMLGEN.SETBINDVALUE() is used to set the bind variable for the SQL query, and DBMS_XMLGEN.CLEARBINDVALUE() is used to clear the bind variable data.

DECLARE
qryCtx DBMS_XMLGEN.CTXTYPE;
v_sql VARCHAR2(100);
v_clob CLOB;
TYPE deptType IS RECORD (department_id NUMBER(4));
v_deprec deptType;
v_refcur SYS_REFCURSOR;
BEGIN
qryCtx := DBMS_XMLGEN.NEWCONTEXT(
'SELECT * FROM employees WHERE department_id = :MID');
-- Open Ref Cursor
OPEN v_refcur FOR
'SELECT department_id FROM departments WHERE location_id = 1700';
-- Loop each department
LOOP
-- Fetch from cursor variable.
FETCH v_refcur INTO v_deprec;
EXIT WHEN v_refcur%NOTFOUND;
DBMS_XMLGEN.SETBINDVALUE( qryCtx, 'MID', v_deprec.department_id);
v_clob := DBMS_XMLGEN.GETXML( qryCtx );
INSERT INTO temp VALUES(v_clob);
END LOOP;
DBMS_XMLGEN.CLOSECONTEXT(qryCtx);
EXCEPTION
WHEN OTHERS THEN
DBMS_XMLGEN.CLOSECONTEXT(qryCtx);
END;

Querying the V$SQL view in SYS user, we can see how many times the SQL query is parsed and executed:

SQL> SELECT parse_calls,executions
FROM v$sql
WHERE sql_text
LIKE 'SELECT * FROM employees WHERE department_id =%';
PARSE_CALLS EXECUTIONS
---------- ----------
1 21

The result shows that the SQL query is executed 21 times but is only parsed once. This shows the value of using bind variables in SQL.

Dealing with Special Characters

Some characters—such as ‘, <, and >—are special characters in XML documents and must be in escaped format, also known as character entities format. For example, the escaped format for ‘ is &apos;, the escaped format for > is &gt;, and the escaped format for < is &lt;. By default, all special characters are escaped. For example, the following SQL query contains a ‘ character:

SELECT DBMS_XMLGEN.GETXML(
'SELECT ''Don''''t escape me!'' AS result FROM dual') 
FROM dual;

The following XML document is generated with the ' escaped:

<?xml version="1.0"?>
<ROWSET>
<ROW>
<RESULT>Don&apos;t escape me!</RESULT>
</ROW>
</ROWSET>

To avoid the character escaping, you can call the DBMS_XMLGEN.SETCONVERTSPECIALCHARS() procedure with FALSE, as in the following example:

DECLARE
qryCtx DBMS_XMLGEN.CTXHANDLE;
BEGIN
qryCtx :=
DBMS_XMLGEN.NEWCONTEXT(
'SELECT ''Don''''t escape me!'' as result FROM dual');
DBMS_XMLGEN.SETCONVERTSPECIALCHARS(qryCtx, FALSE);
:result := DBMS_XMLGEN.GETXML(qryCtx);
DBMS_XMLGEN.CLOSECONTEXT(qryCtx);
END;

The generated XML document will have the following format:

<?xml version="1.0"?>
<ROWSET>
<ROW>
<RESULT>Don't escape me!</RESULT>
</ROW>
</ROWSET>