Oracle Database 10g XML SQL [Electronic resources] : Design, Build Manage XML Applications in Java, C, C++ PL/SQL نسخه متنی

Generating XML from SQL Data with SQL XML Functions

To generate XML from the SQL data stored in object or relational tables and process XML, Oracle XML DB provides support for the SQL/XML standard and a set of Oracle-provided SQL/XML functions.

The SQL/XML standard is part of the ISO/IEC (International Organization for Standardization/International Electrotechnical Commission) 9075 standard, which specifies the SQL standard for XML operations. Table 10-1 lists the SQL/XML functions and describes their functionality in the Oracle Database 10g. Because currently the SQL/XML standard is still a working draft, the syntax of the SQL/XML functions is subject to change in future releases.

Table 10-2 lists the Oracle-provided SQL/XML functions.

If you are familiar with SQL, it should not be difficult to use these SQL/XML functions in the SQL statements. The following is the basic syntax:

SELECT [XMLELEMENT |XMLATTRIBUTES |XMLFOREST|XMLCONCAT |XMLAGG|…]
FROM table_name, [table (XMLSEQUENCE)]
WHERE search_conditions

Using XMLELEMENT(), XMLATTRIBUTES(), and XMLFOREST() functions, you can create XML elements, the attributes for those elements, and XML document fragments. The XML elements and XML document fragments then can be concatenated and aggregated using XMLCONCAT() and XMLAGG(). The XMLSEQUENCE() function is different from the other functions. It creates a collection of XMLType instances, which can then be used to create temporary tables in the FROM clause of the SQL queries.

XMLELEMENT() and XMLATTRIBUTES()

XMLELEMENT() takes an element name and zero or more arguments that make up the element’s content to create the XML element as an instance of XMLType. You can also specify the collection of attributes for the element using XMLATTRIBUTES(). For example, logging in to the HR sample schema, you can create an XML element by using the following SQL command:

SQL> SELECT
2 XMLELEMENT("Employee",
3 XMLATTRIBUTES(employee_id AS "empno",
4 job_id AS "job"),
5 XMLELEMENT("Name",first_name||' '||last_name),
6 'is hired on ',
7 hire_date
8 )AS result
9 FROM employees
10 WHERE rownum=1;

In line 2, an Employee element is defined with two attributes, empno and job. The content of the attributes comes from the employee_id and job_id column in the employees table. Then, a child element of Employee is created, called Name, with its content coming from the concatenation of the first_name and last_name columns. Additionally, you can add mixed content in the query; for example, ‘is hired on ’ is concatenated with the content of the hire_date column in lines 6 and 7, and the text is inserted after the <Name> element. The SQL query produces the following XML element with attributes, along with a child element with mixed content:

<Employee empno="100" job="AD_PRES">
<Name>Steven King</Name>is hired on 17-JUN-87
</Employee>

Note that if the element name or attribute name is not specified, the table column name will be used by default.

SYS_XMLGEN()

Different from XMLELEMENT(), SYS_XMLGEN() function allows creating the <?XML?> XML prolog and adding the XML Processing Instructions (PIs), such as XSLT stylesheet PI, to the created XML documents. For example, you can create an XML document with a prolog with the following SQL command:

SELECT SYS_XMLGEN(XMLELEMENT("Employee",
XMLATTRIBUTES(employee_id AS "empno", job_id AS "job"),
XMLELEMENT("Name",first_name||' '||last_name),
'is hired on ',
hire_date))AS result
FROM EMPLOYEES
WHERE rownum=1;

Executing this query returns the following result:

<?xml version="1.0"?>
<ROW>
<Employee empno="100" job="AD_PRES">
<Name>Steven King</Name>is hired on 17-JUN-87
</Employee>
</ROW>

The SYS_XMLGEN() requires taking one scalar value, a user-defined object type or an XMLType instance as the input. You can also use the XMLFORMAT object to specify the XML formatting options (PIs, root tag, etc.) for the XML document:

SELECT SYS_XMLGEN(XMLELEMENT("Employee",
XMLATTRIBUTES(employee_id AS "empno", job_id AS "job"),
XMLELEMENT("Name",first_name||' '||last_name),
'is hired on ',
hire_date),
XMLFORMAT.createformat('EmployeeList','NO_SCHEMA',
null,'http://www.oracle.com/','http://dburl',
'<?xml-stylesheet href="htmlRend.xsl" type="text/xsl" ?>'))
FROM employees
WHERE rownum <3;

The XML created contains an XSL stylesheet PI and the default root element, the <ROW> element, is replaced by the <EmployeeList>element:

<?xml version="1.0"?>
<?xml-stylesheet href="htmlRend.xsl" type="text/xsl" ?>
<EmployeeList>
<Employee empno="100" job="AD_PRES">
<Name>Steven King</Name>is hired on 17-JUN-87</Employee>
</EmployeeList>

XMLFOREST()

The XMLFOREST() function produces an XML fragment that contains a set of XML elements. The following example adds additional sub-elements to the <Employee> element:

SELECT
XMLELEMENT("Employee",
XMLATTRIBUTES(employee_id AS "empno", job_id AS "job"),
XMLELEMENT("Name",first_name||' '||last_name),
'is hired on ', hire_date,
XMLFOREST(EMAIL, PHONE_NUMBER))AS result
FROM employees
WHERE rownum=1;

This produces the following result:

<Employee empno="100" job="AD_PRES">
<Name>Steven King</Name>is hired on 17-JUN-87
<EMAIL>SKING</EMAIL>
<PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
</Employee>

Note that some of the characters allowed in SQL identifiers are not valid for XML element names. When element or attribute names are not specified as quoted aliases such as using XMLELEMENT(“<element_name>“) or using the AS clauses as we did for the XMLATTRIBUTES() function, the fully escaped character mapping is used to map table column names to the XML element names, such as <EMAIL> and <PHONE_NUMBER> in the XMLFOREST() are fully escaped names from the corresponding table column names. The fully escaped character mapping will convert all of the invalid XML characters in the SQL names, such as the : character, to their Unicode representation in hexadecimal format starting with an x sign. For example, if the following object and table are created, where the object name contains a : character:

CREATE TYPE mydesc AS OBJECT ("my:desc" VARCHAR(200))
/
CREATE TABLE mydesc_tbl of mydesc
/ 
INSERT INTO mydesc_tbl VALUES('fully escaped character mapping');

After inserting the sample data, you can submit an SQL query as follows:

SELECT XMLELEMENT("Test", XMLFOREST("my:desc")) FROM mydesc_tbl;

It gives you the XML element <my_x003A_desc> with the SQL name fully escaped:

<Test><my_x003A_desc>fully escaped character mapping</my_x003A_desc>
</Test>

Because : is not a legal character for the name of XML elements, it is escaped to be x003A, which is its Unicode representation.

If you need to keep the : character in the XML element/attribute names, you need to specify the name aliases for the XML elements and attributes to enable partially escaped character mapping, as shown in the following SQL query:

SELECT XMLELEMENT("Test", XMLFOREST("my:desc" AS "my:desc"))
FROM mydesc_tb

This allows you to create XML elements and attributes with namespaces as follows:

SELECT XMLELEMENT("TEST",
XMLATTRIBUTES('http://xmlns.oracle.com/xml/Employee.xsd'
AS "xmlns:my"), XMLFOREST("my:desc" as "my:desc"))
FROM mydesc_tbl;

The result is

<TEST xmlns:my="http://xmlns.oracle.com/xml/Employee.xsd">
<my:desc>fully escaped character mapping</my:desc></TEST>

The XML document defines a namespace prefix my for the <my:desc> element.

XMLSEQUENCE()

The function XMLSEQUENCE() returns a sequence of XMLTypes in XMLSEQUENCEType, which is a VARRAY of XMLType instances. The following example shows the output of XMLSEQUENCE() when given a SQL cursor expression as the input:

SELECT XMLSEQUENCE(
CURSOR(
SELECT employee_id, first_name, last_name
FROM employees where rownum <3)) AS result
FROM dual;

The result is

XMLSEQUENCETYPE(XMLTYPE( <ROW>
<EMPLOYEE_ID>100</EMPLOYEE_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>King</LAST_NAME>
</ROW>
), XMLTYPE( <ROW>
<EMPLOYEE_ID>101</EMPLOYEE_ID>
<FIRST_NAME>Neena</FIRST_NAME>
<LAST_NAME>Kochhar</LAST_NAME>
</ROW>
))

The query returns a collection of XMLType instances containing the <EMPLOYEE_ID>, <FIRST_NAME>, and <LAST_NAME>elements in XML. Since the <ROW> element is used within each row of XMLTypes, you need to make sure it is included in the XPath expressions when accessing the XML data returned as follows:

SELECT value(e).extract('/ROW/EMPLOYEE_ID').getClobVal()
FROM TABLE(SELECT XMLSEQUENCE(
CURSOR(
SELECT employee_id, first_name, last_name
FROM employees where rownum <3))
FROM dual) e;

The result is

<EMPLOYEE_ID>100</EMPLOYEE_ID>
<EMPLOYEE_ID>101</EMPLOYEE_ID>

In the first line of the preceding SQL query, the XPath expression /ROW/EMPLOYEE_ID is used to extract the employees’ IDs.

XMLCONCAT()

XMLCONCAT() takes an XMLSEQUENCEType or a number of XMLType instances and returns an XML fragment with all the passed-in XML content concatenated. For example, the following SQL concatenates all XML elements within an XML sequence:

SELECT (XMLCONCAT(
XMLSEQUENCE(
CURSOR(SELECT * FROM employees WHERE rownum<3))))
FROM dual;

You can also use XMLCONCAT() with a set of XMLTypes, as shown in the following SQL query:

SELECT XMLCONCAT(XMLELEMENT("Email",email),
XMLELEMENT("Name", first_name||' '||last_name))
FROM employees
WHERE ROWNUM<3;

This function is useful when creating a single XML fragment from multiple XMLTypes.

XMLAGG()

The XMLAGG() function aggregates all the XML elements returned by the SQL query into an XML document fragment and allows an optional ORDER BY clause to order the XML values during the aggregation. The following SQL query returns all the employees ordered by their first_name:

SELECT XMLAGG(value(e)
ORDER BY EXTRACTVALUE(value(e),'/ROW/FIRST_NAME') DESC NULLS FIRST)
FROM TABLE(XMLSEQUENCE(CURSOR(SELECT first_name, last_name,salary
FROM employees))) e
WHERE EXTRACTVALUE(value(e), '/ROW/SALARY') BETWEEN 12000 AND 18000;

The difference between XMLCONCAT() and XMLAGG() is that XMLAGG() operates over the rows of data returned by the SQL query, while XMLCONCAT() doesn’t work across rows.

SYS_XMLAGG()

SYS_XMLAGG() provides similar functionality to XMLAGG() except that SYS_XMLAGG() adds an additional <ROWSET>root element and the <?XML?> prolog to make sure the result is a well-formed XML document. Like SYS_XMLGEN(), SYS_XMLAGG() can use the XMLFORMAT object to specify the output formats. However, you need to make sure the input of SYS_XMLAGG() is a scalar type, an object type or an XMLType instance. Therefore, if multiple XMLType objects are needed for SYS_XMLAGG(), you need to use XMLCONCAT() to concatenate them into one XMLType object instead of passing them directly to the SYS_XMLAGG():

SELECT SYS_XMLAGG(XMLCONCAT(value(e)),
XMLFORMAT.createFormat('EmployeeList'))
FROM TABLE(XMLSEQUENCE(CURSOR(SELECT first_name, last_name,salary
FROM employees))) e
WHERE EXTRACTVALUE(value(e), '/ROW/SALARY') BETWEEN 12000 AND 18000;

XMLCOLATTVAL()

The XMLCOLATTVAL() function creates an XML fragment containing a set of <column> elements where the name attributes are used to keep the column names or the name aliases provided by SQL queries. For example, the following SQL query returns three <column> elements with their name attributes coming from the hire_date and job_id column names and the dept name alias:

SELECT XMLELEMENT("Employee", 
XMLATTRIBUTES(first_name||' '||last_name AS "name"), 
XMLCOLATTVAL(hire_date, job_id, department_id AS "dept")) AS "result"
FROM employees e
WHERE rownum=1

The result is

<Employee name="Steven King">
<column name = "HIRE_DATE">17-JUN-87</column>
<column name = "JOB_ID">AD_PRES</column>
<column name = "dept">90</column>
</Employee>

This function is useful when table column names contain illegal XML characters.

UPDATEXML()

The UPDATEXML() function is a useful XML extension function that accepts an XMLType instance and a set of XPath expression and string value pairs to update the XPath-referred elements or attributes with the provided values. It returns a new transient XMLType instance consisting of the original XMLType instance with appropriate XML nodes updated.

The UPDATEXML() function offers multiple updates on the XML document and allows multiple namespace declarations:

SELECT UPDATEXML(column_name, 'XPath1', 'text1', …'XPathN', 'textN','Namespace1 NamespaceN') FROM table_name;

The following example illustrates how to use namespaces in an update:

SQL> SELECT UPDATEXML(XMLType(
2 '<Employee xmlns:app1="www.example.com/ns1" 
xmlns:app2="www.example.com/ns2">
3 <Name app1:type="Customer">Janet Jones</Name>
4 <Job app2:type="IT">Manager</Job>
5 <Salary app2:type="Hidden">12000</Salary>
6 <Commission app2:type="Hidden">3400</Commission>
7 </Employee>'), 
8 '/Employee/Name/text()', 'Janet Lee', 
9 '/Employee/Name/@app1:type', 'Important Customer', 
10 '/Employee/Job/@app2:type', 'Hidden', 
11 '/Employee//*[@app2:type="Hidden"]',null, 
12 'xmlns:app1="www.example.com/ns1"
13 xmlns:app2="www.example.com/ns2"') AS result
14 FROM dual;

The result is

<Employee xmlns:app1="www.example.com/ns1" xmlns:app2="www.example.com/ns2">
<Name app1:type="Important Customer">Janet Lee</Name>
<Job/>
<Salary/>
<Commission/>
</Employee>

The input XML document contains two namespaces, the xmlns:app1=“www.example.com/ns1” and xmlns:app2=“www.example.com/ns2”. However, you don’t have to declare the namespaces for the UPDATEXML() unless the XML elements in the XPath expressions needed to be qualified by the namespaces. For example, you do not have to declare any namespace if you just need to update the /Employee/Name/text() node to be Janet Lee.

However, if XML elements in XPath need to be qualified by the namespaces, you have to declare the namespaces in UPDATEXML() as shown in line 12 and line 13, where namespaces are delimited by whitespaces.

The order of updates is determined by the order of the XPath expressions in the UPDATEXML() from left to right. Updates can be cascaded because each successive update is based on the result of the previous ones. Therefore, after updating the /Employee/Job/@app2:type to be Hidden, the next update, which sets the elements satisfied /Employee//*[@app2:type=“Hidden”] to be NULL, updates the <Job> element in addition to the <Salary> and <Commission> elements.

To update the data stored in the tables, you can use UPDATEXML() in a SQL INSERT statement, as shown in this example:

UPDATE temp SET doc=UPDATEXML(doc,
'/a:foo/a:lastupdate/text()',SYSDATE,
'xmlns:a="http://www.example.com"');

The result is

<foo xmlns="http://www.example.com" xmlns:xsd="http://www.w3c.org/2001/XMLSchema">
<lastupdate xsd:type="date">12-MAY-03</lastupdate>
</foo>

Now we have discussed the built-in SQL/XML functions in Oracle Database 10g to create XML elements, attributes, XML document fragments, and update XML documents. With these functions, you can create XML easily using SQL statements. In the next section, we will look at PL/SQL XML functions that can also be used to create XML supporting more complex logic.