How to process XML data with Oracle PL/SQL with examples?

In my previous article I have given the examples of how to do performance tuning for PostgreSQL with examples. In this article i would like to give you information about the how to process XML data with Oracle PL/SQL with multiple real life examples. Oracle has introduced a separate in built package for processing the xml data named XMLDB package. The article will give information about how to do the xml file processing with multiple scenarios.

What we will see in this article?

XML Parsers for processing data

How to process XML data with Oracle PL/SQL with multiple scenarios.

XML Parsers for processing data :

The XML Parsers are important PL/SQL parsers which we are using to process the XML files. There are so many Java based application will generate the data in XML format and there is necessary to process the xml data and give it to other applications or keep it in human readable format. The first step is to access the XML parser and run the xml parser.

XML Parser Location : $ORACLE_HOME/xdk/plsql/parser

We require to run the xml parser and use its different interfaces of XML Parser to read the xml files. I will not go in to deep and will prepare separate article for the XML Parsers.

FLEXTER XML CONVERTER :

In addition to native XML processing in Oracle PL/SQL, you might encounter scenarios where transforming complex XML structures to industry-standard relational database formats like Snowflake becomes crucial. Tools like Flexter for such XML conversions offer a seamless solution. Flexter specializes in converting intricate XML data into structured, queryable formats, facilitating easy integration with databases like Snowflake. Its intuitive interface and robust capabilities make it a valuable asset in scenarios where complex XML transformations are required.

How to process XML data with Oracle PL/SQL through XMLDB?

In this section I would like to give you the multiple scenarios of processing the XML data with using Oracle PL/SQL through XMLDB package. We will see multiple examples of how to convert , how to select and how to check the data from xml files. In our day to day scenarios we require to perform the multiple xml operations. We might have seen some examples of converting the BLOB/CLOB datatypes in XMLtypes.

Example 1 : How to convert CLOB to XMLtype?

The first example i would like to give this because in many real life applications we are using the CLOB datatypes. To process the data with using XMLDB package we must require to convert the datatype to XMLtype. You can not use the XMLDB operators with using CLOB datatype and we have to convert the CLOB datatype to XMLtype.

Simple Example :

Declare

V_clob CLOB;

V_XMLType XMLType;

BEGIN

V_XMLType : xmlType( V_clob) ; —Use xmlType function to convert CLOB to xmlType.

END;

Example 2 : Finding out the XSD Schema in Oracle database?

There are so many inbound applications in real life scenarios and we require to validate that the correct XML file is coming from correct source. You need to use the XSD schema to verify the file is processed successfully and schema and contents of xml file are correct.

Simple Example :

Declare

V_xmlSchema XMLType;

V_Schema_URL varchar2(500) := ‘ Put_your_schema_URL’;

BEGIN

—step 1 : Delete old schema before register new schema

DBMS_XMLSchema.deleteSchema( V_Schema_URL =>’ABCD.xsd’, delete_option=>DBMS_XMLSchema.Delete_Cascade_Force);

—step 2 : Register new XSD schema

V_xmlSchema :=xmlType(‘Put your xsd schema definition here’);

dbms_xmlschema.registerSchema( V_Schema_URL , V_xmlSchema ,FALSE);

END;

Example 3 : How to do Validation of XML to XSD in Oracle DB

The quality also matters when you are dealing with the XML file types. Checking the format of file is one of the important step while you dealing with xml files. This step will improve the qualty of sending and receiving the XML files in the database. It will give you better control on processing information.

DECLARE

V_clob CLOB;

V_xmldoc XXLType;

BEGIN

V_xmldoc : = xmlType( V_clob )

V_xmldoc := V_xmldoc .createSchemaBasedXML(‘Put-your-schema-URL’);

—-Step 1 Validation of XML format

If V_xmldoc .isSchemaValid() = 1

THEN

V_xmldoc .SchemaValidate();

END IF;

END;

Example 4 : How to run select statement over XML

This is also a big topic and i will create separate article for the same. But Here i would like to give you some important examples for the same.

—Step 1 : You can create separate column of XMLType in table level to store the XML type data.

Create Table T_XML_Example

(

Customer_id number(10),

V_XML_Spec XMLTYPE);

You can also create the XMLTYPE table also,

Create table T_My_XML_Table of XMLTYPE;

If you want to fetch the records in T_My_XML_Table then you require to use few functions like- extactValue and if you need specific data then you need to use some important filter conditions.

Example :

DECLARE

V_Emp_Name varchar2(300);

BEGIN

Select extractValue(Value(ma),’/Project/Employee/Name)

INTO v_temp

From T_My_XML_Table /*Assuming T_My_XML_Table has data of Employee*/,table(XMLSequence(extract(OBJECT_VALUE,’/project/company/employees’)));

End;

The above statement will fetch the data from T_My_XML_Table table.

These are few examples of processing xml data using oracle. This concept is vastly used in so many real life scenarios where we are using the inbound and outbound flows. I hope you like this article. If you like this article or if you have any concerns with the same kindly comment in comments section.