Systems Engineering and RDBMS

Converting Relational Data to XML and vice-versa in Oracle

Posted by decipherinfosys on October 30, 2007

In one of our previous blog post, we had covered how to generate XML documents in Oracle. And in subsequent posts, we had covered some of the XML features in SQL Server 2005 – de-construct XML as relational data, difference between typed and un-typed XML, and the difference between element and attribute centric XML.  In this post, we will briefly cover the usage of SQL functions – XMLForest and XMLElement in Oracle to convert relational data into XML and vice-versa.

Let us first create a TEST table and populate it with some data. Connect to SQL*Plus with proper authorization.

CREATE TABLE TEST(EMP_ID NUMBER(9), EMP_Name VARCHAR2(30),BIRTH_DATE DATE)
/

INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (1, ‘Jack’,’20-JAN-65′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (2, ‘John’,’01-FEB-82′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (3, ‘Beth’,’11-OCT-70′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (4, ‘Mary’,’30-SEP-70′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (5, ‘Allen’,’3-MAR-75′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (6, ‘Diana’,’2-NOV-68′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (7, ‘Don’,’31-DEC-80′);

Now using XMLFOREST function, we can generate the XML output. Run following query.

SELECT EMP_ID as Employee_Number,XMLFOREST(EMP_ID, EMP_NAME, BIRTH_DATE) AS XML_DATA
FROM TEST
/

Here is the output. Output is formatted for better readability.

EMPLOYEE_NUMBER        XML_DATA
---------------        -------------------------------------
              1        <EMP_ID>1</EMP_ID>
                       <EMP_NAME>Jack</EMP_NAME>
                       <BIRTH_DATE>1965-01-20</BIRTH_DATE>
              2        <EMP_ID>2</EMP_ID>
                       <EMP_NAME>John</EMP_NAME>
                       <BIRTH_DATE>1982-02-01</BIRTH_DATE>
              3        <EMP_ID>3</EMP_ID>
                       <EMP_NAME>Beth</EMP_NAME>
                       <BIRTH_DATE>1970-10-11</BIRTH_DATE>

In the output shown above, the column values are converted to xml data and column names are converted to tag names and data is enclosed within the tag. We can use XMLELEMENT function to place the user defined tag around the XML for each record. Let us see that.

SELECT EMP_ID as Employee_Number,
XMLELEMENT(“Employee”, XMLFOREST(EMP_ID, EMP_NAME, BIRTH_DATE)) AS XML_DATA
FROM TEST
/

Above SQL will wrap the user defined tag “Employee” around the original record displayed in the above result set. We can also store XML_DATA values in the table in which columns are defined as XMLTYPE. Datatype XMLTYPE has an advantage specially when searching for data in the XML. Using context search we can easily search for specific character or string in the XMLTYPE data.

Above approach shows how to convert relational data to XML data but, how can we convert back XML data into relational data? There are also functions available to do the same. Using EXTRACTVALUE function, we can retrieve data back into relational format from the XML format. Here is the SQL to do that.

SELECT EXTRACTVALUE(XML_DATA,’Employee/EMP_ID’) as EMP_ID,
EXTRACTVALUE(XML_DATA,’Employee/EMP_NAME’) as EMP_NAME,
TO_DATE(EXTRACTVALUE(XML_DATA,’Employee/BIRTH_date’),’MM/DD/YYYY’) as BIRTH_DATE
FROM
(
SELECT EMP_ID as Employee_Number,
XMLELEMENT(“Employee”, XMLFOREST(EMP_ID, EMP_NAME, BIRTH_DATE)) AS XML_DATA
FROM TEST
) A
/

Here is the output.

    EMP_ID EMP_NAME   BIRTH_DATE
---------- ---------- ----------
         1 Jack
         2 John
         3 Beth
         4 Mary
         5 Allen
         6 Diana
         7 Don

As you can see from above, we got the Employee ID and Employee Name but we didn’t get any data for Birth_Date column and we know that there is data for that column. This is because tags in XML are case-sensitive. We have defined the tag as BIRTH_DATE and we are trying to retrieve data as BIRTH_date. If we change the tag to BIRTH_DATE in EXTRACTVALUE function, we will get the data for birth date column as well. So one thing we have to keep in mind is that, we have to be extra cautious when dealing with XML data. One mismatch in upper or lower case and we may not get the desired data.

SELECT EXTRACTVALUE(XML_DATA,’Employee/EMP_ID’) as EMP_ID,
EXTRACTVALUE(XML_DATA,’Employee/EMP_NAME’) as EMP_NAME,
TO_CHAR(EXTRACTVALUE(XML_DATA,’Employee/BIRTH_DATE’),’MM/DD/YYYY’) as BIRTH_DATE
FROM
(
SELECT EMP_ID as Employee_Number,
XMLELEMENT(“Employee”, XMLFOREST(EMP_ID, EMP_NAME, BIRTH_DATE)) AS XML_DATA
FROM TEST
) A
/

Here is the complete output.

EMP_ID EMP_NAME   BIRTH_DATE
———- ———- ———-
1 Jack       01/20/1965
2 John       02/01/1982
3 Beth       10/11/1970
4 Mary       09/30/1970
5 Allen      03/03/1975
6 Diana      11/21/1968
7 Don        12/31/1980

In future blogs, we will cover more about some more complex and different XML options. This is just to demonstrate the simple approach to deal with XML data type.

One Response to “Converting Relational Data to XML and vice-versa in Oracle”

  1. […] by decipherinfosys on November 10th, 2007 In one of our previous blog post, we had covered, how in Oracle can we convert relational data to XML in vice-versa. Today we will […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: