Oracle XML DB is a set of built-in
high-performance storage and retrieval technologies developed especially
for XML. Oracle XML DB provides W3C XSLT Support that allows for a database-based
transformation of in-memory or on disk XML documents.
XML documents have structure
but no format. eXtensible Stylesheet Language (XSL)
can be used to add format to the XML documents. It can map XML elements
into other formatting or markup languages such as HTML, WML etc. XMLType
instances or XML data stored in XMLType tables, columns, or views in
the Oracle XML DB, can be transformed
into the desired format using XSL stylesheets and the following APIs
provided with the database:
XMLType Datatype's Transform()
Method
The XMLTransform()
SQL function
PL/SQL DOM APIs for XMLType
In the first two approaches,
since the transformation takes place close to the data, Oracle XML DB
can optimize features such as the memory usage, I/O operations, and network
traffic etc. required to perform the transformation.
This document describes how
XML transformation can be achieved using the above mentioned approaches
supplemented with code snippets. As an
example we will consider an XML document that contains the details of
a credit card account. This document will be transformed into an HTML
format using the above mentioned approaches.
Execute the SQL scripts that will create the
getXML() and getXSL() functions inside
the database using SQL*Plus. The getXML()
function returns the account XML document that needs to be transformed
and getXSL() function returns the
XSL stylesheet that is used for transforming the XML into HTML.
Ensure to use the same database schema for running all
the scripts provided in this document.
Note: In this document, for simplicity, getXML()
and getXSL() function scripts use small size XML documents.
In PL/SQL, there is a limitation of 64K on the size of a String
constant. If an XML document greater than 64K is required, the best approach
is to load the document from a file. For details on this approach, please
refer to the "Oracle XML DB Utilities Package" in the XML DB Sample
Corner at /sample_code/tech/xml/xmldb/index.html.
Description
Let us now look at the different approaches for XML
transformation in detail by using:
XML data can be transformed to HTML or any other
markup language using the XMLType.transform() method provided
by the XMLType datatype in the database. Here, since the transformation takes place close
to the data, the applications using this method can benefit
from the usage of the native transform capability provided by the Oracle
XML DB.
Listing 1 shows how
an XMLType instance can be transformed using the
XMLType.transform() method. In this snippet, the function
created, getAccountsHTML, transforms the XML document containing the
Account details into an HTML document using the XMLType.transform()
method.
Listing 1:
CREATE OR REPLACE FUNCTION getAccountsHTML RETURN CLOB IS
-- Define the local variables xmldata XMLType; xsldata XMLType; html XMLType;
BEGIN -- Get the XML document using the getXML() function defined in the database. -- Since XMLType.transform() method takes XML data as XMLType instance, -- use the XMLType.createXML method to convert the XML content received -- as CLOB into an XMLType instance. xmldata := XMLType.createXML(getxml());
-- Get the XSL Stylesheet using the getXSL() function defined in the database. -- Since XMLType.transform() method takes an XSL stylesheet as XMLType instance, -- use the XMLType.createXML method to convert the XSL content received as CLOB -- into an XMLType instance. xsldata := XMLType.createXML(getxsl());
-- Use the XMLtype.transform() function to get the transformed XML instance. -- This function applies the stylesheet to the XML document and returns a transformed -- XML instance. html := xmldata.transform(xsldata);
-- Return the transformed XML instance as a CLOB value. RETURN html.getClobVal();
EXCEPTION WHEN OTHERS THEN raise_application_error (-20102, 'Exception occurred in getAccountsHTML :'||SQLERRM); END getAccountsHTML; /
Run the script in Listing 1 above in SQL*Plus
to create the getAccountsHTML function in the database
as this function is used in the script defined later in this document.
The XMLTransform() SQL
function
The XMLTransform() SQL function
takes in an XMLType instance and an XSLT stylesheet for transformation
purposes. It applies the stylesheet to the XML document and returns
the processed output as XML, HTML, and so on, as specified in the XSL
stylesheet. Typically XMLTransform() can be used when
retrieving or generating XML documents stored as XMLType in Oracle XML DB.
Listing 2 describes how
an XML document can be transformed using the XMLTransform()
function. This function takes in the XML document to be transformed
and the stylesheet that needs to be applied as XMLType instances.
The XML and XSL content that is returned as CLOB by the getXML()
and the getXSL() functions respectively are converted
into XMLType instances using the XMLType.createXML
method. The output of the following SQL will be the transformed Account
details in HTML format.
Listing 2:
SQL> SELECT XMLTransform(XMLType.createXML(getxml()),XMLType.createXML(getxsl())) AS AccountDetails FROM dual;
Run the SQL command in Listing 2 above in SQL*Plus
to view the transformed output.
PL/SQL APIs for XMLType
PL/SQL Application Program Interfaces (APIs) for
XMLType includes :
PL/SQL DOM API for XMLType (package
DBMS_XMLDOM): For accessing XMLType objects(XML
schema-based and non-schema-based documents).
PL/SQL XML Parser API for XMLType (package
DBMS_XMLPARSER): For accessing the contents and structure
of XML documents.
PL/SQL XSLT Processor for XMLType (package
DBMS_XSLPROCESSOR): For transforming XML documents to
other formats using XSLT.
Listing 3 describes how
the PL/SQL APIs for XMLType are used to transform an XML document.
In this snippet, the getPLSAccountsHTML
function transforms the XML document containing the Account
details into HTML format using the PL/SQL API's .
Listing 3:
CREATE OR REPLACE FUNCTION getPLSAccountsHTML RETURN CLOB IS
-- Declare the local variables xmldoc CLOB; xsldoc CLOB; myParser dbms_xmlparser.Parser; indomdoc dbms_xmldom.domdocument; xsltdomdoc dbms_xmldom.domdocument; xsl dbms_xslprocessor.stylesheet; outdomdocf dbms_xmldom.domdocumentfragment; outnode dbms_xmldom.domnode; proc dbms_xslprocessor.processor; html CLOB;
BEGIN -- Get the XML document using the getXML() function defined in the database xmldoc := getXML(); -- Get the XSL Stylesheet using the getXSL() function defined in the database xsldoc := getXSL();
-- Get the new xml parser instance myParser := dbms_xmlparser.newParser; -- Parse the XML document dbms_xmlparser.parseClob(myParser, xmldoc); -- Get the XML's DOM document indomdoc := dbms_xmlparser.getDocument(myParser);
-- Parse the XSL document dbms_xmlparser.parseClob(myParser, xsldoc); -- Get the XSL's DOM document xsltdomdoc := dbms_xmlparser.getDocument(myParser);
xsl := dbms_xslprocessor.newstylesheet(xsltdomdoc, ''); -- Get the new xsl processor instance proc := dbms_xslprocessor.newProcessor;
-- Apply stylesheet to DOM document outdomdocf := dbms_xslprocessor.processxsl(proc, xsl, indomdoc); outnode := dbms_xmldom.makenode(outdomdocf);
-- Write the transformed output to the CLOB dbms_xmldom.writetoCLOB(outnode, html);
-- Return the transformed output return(html); EXCEPTION WHEN OTHERS THEN raise_application_error(-20103, 'Exception occurred in getPLSAccountsHTML Function :'||SQLERRM); END getPLSAccountsHTML; /
Run the script in Listing 3 above in SQL*Plus
to create the getPLSAccountsHTML function in the database
as this function is used in the script defined later in this document.
View Transformed XML document
Listing 4 details an anonymous
PL/SQL block which calls the getAccountsHTML
and getPLSAccountsHTML
functions provided in the 'XMLType Datatype's transform() method
and PL/SQL APIs for XMLType' sections above to transform the Account XML document into HTML.
Listing 4: --Enable server output set serveroutput on
--Set Line size set line 255
-- Anonymous block to call the functions and display the -- transformed output. DECLARE -- This recursive procedure is to overcome the buffer -- constraints of DBMS_OUTPUT.Put_Line procedure PROCEDURE Show_Message(pmv_Msg_in IN CLOB) IS BEGIN IF LENGTH(pmv_Msg_in) > 255 THEN DBMS_OUTPUT.Put_Line(SUBSTR(pmv_Msg_in,1,255)); Show_Message(SUBSTR(pmv_Msg_in,256,LENGTH(pmv_Msg_in))); ELSE DBMS_OUTPUT.Put_Line(pmv_Msg_in); END IF; END; BEGIN DBMS_OUTPUT.ENABLE(1000000);
--Call the function that uses XMLType.transform() method --to display the account details as HTML
dbms_output.put_line('***************************************************'); dbms_output.put_line('Transformed output using XMLType.transform() method'); dbms_output.put_line('***************************************************'); Show_Message(getAccountsHTML());
--Call the function that PL/SQL API for XMLType to display --the account details as HTML
dbms_output.put_line('************************************'); dbms_output.put_line('Transformed output using PL/SQL APIs'); dbms_output.put_line('************************************'); Show_Message(getPLSAccountsHTML());
END; /
Run the script in Listing 4 above in SQL*Plus to see the transformed output.
Bookmark
