Archives: The easy way of working with XML in Oracle database (part 2)
In this part I’ll look at some features of Oracle Database for getting the data out in XML format, with SQL code only. In part 1 I looked at some features for reading/parsing XML with pure SQL.
Populating the tables for examples
Loading the departments into table DEPARTMENTS.
CREATE TABLE departments AS SELECT dep.* FROM xml, XMLTABLE ('/company/employees/item[1]/departments/item' PASSING x COLUMNS id FOR ORDINALITY, name VARCHAR2 (100 CHAR) PATH 'name') dep;
Employee data to table EMPLOYEES.
CREATE TABLE employees AS SELECT emp.id, emp.first_name, emp.last_name, emp.country, dep.id active_department_id FROM xml, departments dep, XMLTABLE ( '/company/employees/item' PASSING xml.x COLUMNS id NUMBER PATH '@id', first_name VARCHAR2 (10 CHAR) PATH 'first_name', last_name VARCHAR2 (10 CHAR) PATH 'last_name', country VARCHAR2 (10 CHAR) PATH 'country', active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name') emp WHERE dep.name = emp.active_department;
Automatic XML generation + XML Stylesheet
Oracle provides functions sys_xmlgen and sys_xmlagg to automatically generate XML document based on table data. sys_xmlgen adds XML tags around a single value (returns XMLType data type from a single value) and sys_xmlagg aggregates/encloses rows into one XML document. Both of these functions also accept XMLFormat formatting parameter, that can be used to override the default enclosing XML tag name.
SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) company_xml FROM (SELECT SYS_XMLAGG (emp_xml, XMLFormat (enclTag => 'EMPLOYEES')) x FROM ( SELECT tmp_id, SYS_XMLAGG (xml_col, XMLFormat (enclTag => 'ITEM')) emp_xml FROM (SELECT * FROM (SELECT e.id tmp_id, SYS_XMLGEN (e.id) id, SYS_XMLGEN (e.first_name) first_name, SYS_XMLGEN (e.last_name) last_name, SYS_XMLGEN (e.country) country, SYS_XMLGEN (d.name, XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department FROM employees e, departments d WHERE e.active_department_id = d.id) UNPIVOT ((xml_col) FOR tmp_col IN (id, first_name, last_name, country, active_department))) GROUP BY tmp_id) UNION ALL SELECT SYS_XMLGEN ('Some Comany name', XMLFormat (enclTag => 'NAME')) x FROM DUAL)
The query is quite long, but the idea behind it is pretty simple – generate a XMLType row for every data attribute and use SYS_XMLAGG to group and aggregate the generated XMLType rows into desirable XML format.
And the result is one XML document.
<?xml version="1.0"?> <COMPANY> <EMPLOYEES> <ITEM> <ID>1</ID> <ACTIVE_DEPARTMENT>DBA</ACTIVE_DEPARTMENT> <COUNTRY>Estonia</COUNTRY> <LAST_NAME>Kerm</LAST_NAME> <FIRST_NAME>Ilmar</FIRST_NAME> </ITEM> <ITEM> <ID>2</ID> <ACTIVE_DEPARTMENT>Development</ACTIVE_DEPARTMENT> <COUNTRY>Estonia2</COUNTRY> <LAST_NAME>Kerm2</LAST_NAME> <FIRST_NAME>Ilmar2</FIRST_NAME> </ITEM> </EMPLOYEES> <NAME>Some Comany name</NAME> </COMPANY>
If the XML is still not at the correct format, then XSL Transformation can be applied to convert the output to another XML format.
XSL Transformation is an XML document, that describes the rules how to convert one XML document to another XML. For this example I wrote the following XSLT document:
<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="COMPANY"> <company> <name><xsl:value-of select="NAME"/></name> </company> <employees> <xsl:for-each select="EMPLOYEES/ITEM"> <item> <xsl:attribute name="id"> <xsl:value-of select="ID" /> </xsl:attribute> <first_name><xsl:value-of select="FIRST_NAME" /></first_name> <last_name><xsl:value-of select="LAST_NAME" /></last_name> <country><xsl:value-of select="COUNTRY" /></country> <departments> <item active="true"> <name><xsl:value-of select="ACTIVE_DEPARTMENT" /></name> </item> </departments> </item> </xsl:for-each> </employees> </xsl:template> </xsl:stylesheet>
Save it to XML table named comp_xml_cslt.
CREATE TABLE comp_xml_xslt of xmltype; INSERT INTO comp_xml_xslt VALUES ('<?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> ... </xsl:stylesheet> '); COMMIT;
To apply the XSL Transformation to source XML, Oracle has a function XMLTransform(source_xml, transformation_xml). Both arguments are XML documents of XMLType and this function returns transformed XML document.
WITH company AS (SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) xml FROM (SELECT SYS_XMLAGG ( emp_xml, XMLFormat (enclTag => 'EMPLOYEES')) x FROM ( SELECT tmp_id, SYS_XMLAGG ( xml_col, XMLFormat (enclTag => 'ITEM')) emp_xml FROM (SELECT * FROM (SELECT e.id tmp_id, SYS_XMLGEN (e.id) id, SYS_XMLGEN (e.first_name) first_name, SYS_XMLGEN (e.last_name) last_name, SYS_XMLGEN (e.country) country, SYS_XMLGEN (d.name, XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department FROM employees e, departments d WHERE e.active_department_id = d.id) UNPIVOT ((xml_col) FOR tmp_col IN (id, first_name, last_name, country, active_department))) GROUP BY tmp_id) UNION ALL SELECT SYS_XMLGEN ('Some Comany name', XMLFormat (enclTag => 'NAME')) x FROM DUAL)) SELECT XMLTRANSFORM (company.xml, comp_xml_xslt.object_value) FROM company, comp_xml_xslt
In this query, COMPANY inline view is the same XML generating query as in the example before, but the XMLTRANSFORM function in main query is used apply the transformation. Result is below:
<company> <name>Some Comany name</name> </company> <employees> <item id="1"> <first_name>Ilmar</first_name> <last_name>Kerm</last_name> <country>Estonia</country> <departments> <item active="true"> <name>DBA</name> </item> </departments> </item> <item id="2"> <first_name>Ilmar2</first_name> <last_name>Kerm2</last_name> <country>Estonia2</country> <departments> <item active="true"> <name>Development</name> </item> </departments> </item> </employees>
XQuery
Oracle also implements the standard XQuery language, that can be used for querying XML data and Oracle has provided some XQuery views to access relational database tables.
SELECT XMLQuery( 'for $emp in ora:view("EMPLOYEES")/ROW return <item id="{$emp/ID}"> {$emp/FIRST_NAME} {$emp/LAST_NAME} {$emp/COUNTRY} <departments> {for $dep in ora:view("DEPARTMENTS")/ROW return <item active="{if ($dep/ID eq $emp/ACTIVE_DEPARTMENT_ID) then "true" else ("false")}">{$dep/NAME}</item>} </departments> </item>' RETURNING CONTENT) x FROM DUAL
And the XML result is:
<item id="1"><FIRST_NAME>Ilmar</FIRST_NAME><LAST_NAME>Kerm</LAST_NAME><COUNTRY>Estonia</COUNTRY><departments><item active="false"><NAME>Development</NAME></item><item active="true"><NAME>DBA</NAME></item></departments></item><item id="2"><FIRST_NAME>Ilmar2</FIRST_NAME><LAST_NAME>Kerm2</LAST_NAME><COUNTRY>Estonia2</COUNTRY><departments><item active="true"><NAME>Development</NAME></item><item active="false"><NAME>DBA</NAME></item></departments></item>
Archives: The easy way of working with XML in Oracle database (part 1)
I have tried to read Oracle XMLDB Developer Guide and my first reaction was that parsing XML is very complicated in Oracle… And lately I got a task to rewrite some PL/SQL code to use a different SOAP service and the existing code had a few hundred rows just to parse XML (with XML DOM API)! Actually, starting from 10.2, this job is much easier…
Here I will go over some methods for working with XML, that are usable directly from SQL. In this first part, reading and extracting data from XML.
XML for the examples
<?xml version="1.0" encoding="utf-8"?>Some Company name Ilmar Kerm Estonia Development DBA Ilmar2 Kerm2 Estonia2 Development DBA
Load the XML data to a table, to special XMLTYPE data type.
create table xml ( x xmltype ); insert into xml values ( xmltype.createxml('<?xml version="1.0" encoding="utf-8"?>')); COMMIT; Some Company name ...
Extracting a single value
extractValue can be used to extract a single value from XML using XPath expression.
SQL> SELECT extractValue(x, '/company/name') FROM xml; EXTRACTVALUE(X,'/COMPANY/NAME') ------------------------------- Some Company name
extractValue only works with single values, otherwise exception will be raised.
SQL> SELECT extractValue(x, '/company/employees/item') FROM xml; SELECT extractValue(x, '/company/employees/item') FROM xml * ERROR at line 1: ORA-19025: EXTRACTVALUE returns value of only one node
Query XML as relational object
Oracle Database, since 10g, has a very easy way to map XML to a relational object and query it with SQL, so no PL/SQL code is needed for parsing XML – the XMLTABLE function.
SQL> SELECT emp.* FROM xml, XMLTABLE ( '/company/employees/item' PASSING x COLUMNS id NUMBER PATH '@id', first_name VARCHAR2 (10 CHAR) PATH 'first_name', last_name VARCHAR2 (10 CHAR) PATH 'last_name', country VARCHAR2 (10 CHAR) PATH 'country', active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name' ) emp; ID FIRST_NAME LAST_NAME COUNTRY ACTIVE_DEPARTMENT ---------- ---------- ---------- ---------- -------------------- 1 Ilmar Kerm Estonia DBA 2 Ilmar2 Kerm2 Estonia2 Development
XmlTable takes the following arguments:
‘/company/employees/item’ | XPath expression of the “row” |
PASSING x | Source of XML data – XMLTYPE data type |
COLUMNS id NUMBER PATH ‘@id’, first_name VARCHAR2 (10 CHAR) PATH ‘first_name’, last_name VARCHAR2 (10 CHAR) PATH ‘last_name’, country VARCHAR2 (10 CHAR) PATH ‘country’, active_department VARCHAR2 (20 CHAR) PATH ‘departments/item[@active=”true”]/name’ |
Column definitions with the corresponding Oracle data type and XPath expression |
After XML is readable as a relational table, all the power of Oracle SQL can be used for querying.
Categories
- Blog entry (95)
- Event (5)