This BLOG is about processing XML in PL/SQL code and process the data into the database.

Oracle supports this through the  XMLDB package. http://www.oracle.com/technetwork/database/features/xmldb/index.html

I’m going to show you some handy operations. I will show you how to convert, check and select data from xml files, all the right tools to work with XML in Oracle Database.

Converting clob to XMLtype

Converting a CLOB to XMLtype is used for example when you are reading the files from a file location into the database. Once you have the CLOB in the database, you cannot use the XMLDB operators until you have converted the CLOB to an XMLType.

DECLARE
l_clob CLOB;
xmlDoc XMLType;

BEGIN
–XMLtype function will convert the l_clob and put it into the xmlDoc variable.
xmlDoc := xmlType(l_clob);

END;

Registering an XSD in Oracle Database
Checking an XML file, especially when the XML comes from an external resource, is always a valid action to perform. If you want to be sure that the information in the file will be processed successfully and in a correct matter, you can use an XSD schema to verify the schema and contents of the XML. The ‘YourSchemaURL’ is used later on for validation.

DECLARE
xmlSchema XMLType;
schemaURL VARCHAR2(256) := ‘YourSchemaURL’;

BEGIN

–Delete any old schema before registering
DBMS_XMLSchema.deleteSchema(schemaurl=>’KOFAXXML.xsd’, delete_option=>DBMS_XMLSchema.Delete_Cascade_Force);

–Register the xsd schema, FALSE will overwrite the default ‘local’ setting,
–making the schema globally available in your database.
xmlSchema := xmlType(‘Put your xsd schema definition here, in text or variable)
dbms_xmlschema.registerSchema(schemaURL, xmlSchema, FALSE);

END;

 

Validating an XML to an XSD in Oracle Database
isSchemaValid will check if the file is XMLType worthy, next .schemaValidate will check it against the XSD.  If one fails , both operators will cast an error indicating what is not correct in the xmlType. Understand that checking the format of your  file will improve the quality of sending and receiving XML files in the database because you have better control over your information and the processing of that information.

DECLARE

l_clob CLOB;
xmlDoc XMLType;

BEGIN

xmlDoc := xmlType(l_clob);
xmlDoc := xmldoc.createSchemaBasedXML(‘YourSchemaURL’);

–Validate general xml format, if not valid it will cast an error
IF xmldoc.isSchemaValid() = 1
–Validate with XSD
THEN  xmldoc.schemaValidate();

END IF;

END;

 

Running select statements over an XML
For storing an XML file in the database you will need to have a table or column of XMLType. Creating a column would look like this:

CREATE TABLE example(

MyXML_id NUMBER(4),

XML_spec XMLTYPE);

 

In this example I will use an XMLType table.

–Create a table of xml type
CREATE TABLE MY_XML_TABLE OF XMLTYPE

–use your favorite query to insert xml data. The xmltype(l_clob) might come in handy here.

To get the following statement working, you might need to tweak it a little bit to your own situation. You can find some example files in the ZIP at the end of this blog.

 

DECLARE
v_name varchar2(500);

BEGIN

SELECT
extractValue(value(ma), ‘/EMPLOYEES/NAME)

INTO v_temp
FROM MY_XML_TABLE , table(XMLSequence(extract(OBJECT_VALUE, ‘/COMPANY/EMPLOYEES’)))
where extractValue(value(ma), ‘/COMPANY/EMPLOYEES/EMPLOYEEID’)= ‘1’

 

Conclusion
Working with XML in Oracle DB can be tricky if you are not familiar with the possibilities and right syntaxes. Once you get to know the options you will find that Oracle Database has more than enough options to handle all sorts of requirements!

References:

Source code:

Link

Oracle’s resources on XMLDB
http://www.oracle.com/technetwork/database/features/xmldb/index.html

developers guide:
http://docs.oracle.com/cd/E11882_01/appdev.112/e16659/toc.htm