Capgemini Oracle Blog

Capgemini Oracle Blog

Opinions expressed on this blog reflect the writer’s views and not the position of the Capgemini Group

Parsing XML in Oracle Database

Category : Database

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

Oracle supports this through the  XMLDB package.

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);


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';


--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);



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.


l_clob CLOB; xmlDoc XMLType;


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();




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:





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);


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!


Source code:


Oracle’s resources on XMLDB

developers guide:

About the author

Martijn van der Kamp
Martijn van der Kamp
I am an Oracle Integration Consultant. My specialization lay in the areas of SOA Suite, BPM Suite, BAM and databases. Colleagues think high of my social skills and customer focus. Furthermore I'm known for my drive and enthusiasm. Within projects I take great responsibility for the work which I deliver, this will act itself in terms of quality and timeliness of delivery.
2 Comments Leave a comment
Hi Martijn, Thanks for sharing your this specific know how of XML Regards Donald
I personally found this specific post , “Parsing XML in Oracle Database | Capgemini Worldwide”, pretty entertaining and the blog post was indeed a very good read. Regards,Leo

Leave a comment

Your email address will not be published. Required fields are marked *.