xml – Gokhan Atil’s Blog

Python for Data Science – Importing XML to Pandas DataFrame

In my previous post, I showed how easy to import data from CSV, JSON, Excel files using Pandas package. Another popular format to exchange data is XML. Unfortunately Pandas package does not have a function to import data from XML so we need to use standard XML package and do some extra work to convert the data to Pandas DataFrames.

Here’s a sample XML file (save it as test.xml):

<?xml version="1.0"?>
<data>
    <customer name="gokhan" >
        <email>gokhan@gmail.com</email>
        <phone>555-1234</phone>
    </customer>
    <customer name="mike" >
        <email>mike@gmail.com</email>
    </customer>    
    <customer name="john" >
        <email>john@gmail.com</email>
        <phone>555-4567</phone>
    </customer>  
    <customer name="david" >
        <phone>555-6472</phone>
        <address>
            <street>Fifth Avenue</street>
        </address>
    </customer>      
</data>

<?xml version="1.0"?>

<data>

<email>gokhan@gmail.com</email>

</customer>

<email>mike@gmail.com</email>

</customer>

<email>john@gmail.com</email>

</customer>

<street>Fifth Avenue</street>

</address>

</customer>

</data>

We want to convert his to a dataframe which contains customer name, email, phone and street:

     name             email     phone        street
0  gokhan  gokhan@gmail.com  555-1234          None
1    mike    mike@gmail.com      None          None
2    john    john@gmail.com  555-4567          None
3   david              None  555-6472  Fifth Avenue

name email phone street

0 gokhan gokhan@gmail.com 555-1234 None

1 mike mike@gmail.com None None

2 john john@gmail.com 555-4567 None

3 david None 555-6472 Fifth Avenue

As you can see, we need to read attribute of an XML tag (customer name), text value of sub elements (address/street), so although we will use a very simple method, it will show you how to parse even complex XML files using Python.

November 3, 2017Gokhan Atil12 CommentsBig Datapandas, xml

How to Import Data (to Oracle RDBMS) from XML

I wrote a blog post about how to import data from a XML file to Oracle about 7 years ago. I demonstrated how we can use DBMS_XMLSTORE package to parse XML data. Yesterday, I replied a question from a blog reader about that post. He had problem with running my sample script, I did troubleshoot and find out the problem with his script, and while testing the sample script, I noticed that I can write much better script.

This is my sample XML (employees.xml):

<EMPLOYEES>
<EMPLOYEE>
<NO>1</NO>
<NAME>Gokhan Atil</NAME>
</EMPLOYEE>
<EMPLOYEE>
<NO>2</NO>
<NAME>Ned Flanders</NAME>
</EMPLOYEE>
<EMPLOYEE>
<NO>3</NO>
<NAME>Agnes Skinner</NAME>
</EMPLOYEE>
<EMPLOYEE>
<NO>4</NO>
<NAME>Milhouse Van Houten</NAME>
</EMPLOYEE>
</EMPLOYEES>

<NAME>Gokhan Atil</NAME>

</EMPLOYEE>

<NAME>Ned Flanders</NAME>

</EMPLOYEE>

<NAME>Agnes Skinner</NAME>

</EMPLOYEE>

<NAME>Milhouse Van Houten</NAME>

</EMPLOYEE>

</EMPLOYEES>

I created a table to load the data and a directory link so we can access the file:

CREATE TABLE empfromxml ( no NUMBER, name VARCHAR2(50 ));
CREATE DIRECTORY xmltest AS '/oracle/xmltest';

1 2	CREATE TABLE empfromxml ( no NUMBER, name VARCHAR2(50 )); CREATE DIRECTORY xmltest AS '/oracle/xmltest';

August 26, 2016Gokhan Atil6 CommentsOraclexml

How to Generate XML from the Oracle Database

Oracle provides plenty of XML generation methods, I’ll try to demonstrate two of them: SYS_XMLGEN and DBMS_XMLQUERY.

I’ll start with creating a simple table and fill it with sample data:

CREATE TABLE customers (
customer_name VARCHAR2 (30),
phone VARCHAR2(11));

INSERT INTO customers VALUES ( 'GOKHAN', '5421000100' );
INSERT INTO customers VALUES ( 'JACK', '5426661010' );
INSERT INTO customers VALUES ( 'JHON', '5352004050' );
COMMIT;

CREATE TABLE customers (

customer_name VARCHAR2 (30),

phone VARCHAR2(11));

INSERT INTO customers VALUES ( 'GOKHAN', '5421000100' );

INSERT INTO customers VALUES ( 'JACK', '5426661010' );

INSERT INTO customers VALUES ( 'JHON', '5352004050' );

COMMIT;

To be able to use SYS_XMLGEN, I’ll create an object to map our data:

CREATE OR REPLACE TYPE customers_map_type AS OBJECT (
customer_name VARCHAR2 (30),
phone VARCHAR2(11));

CREATE OR REPLACE TYPE customers_map_type AS OBJECT (

customer_name VARCHAR2 (30),

phone VARCHAR2(11));

March 16, 2010Gokhan Atil2 CommentsOraclesys_xmlagg, sys_xmlgen, xml

How to Import Data from XML

One of my blog readers asked me how to import data from XML file. There are lots of ways to do it but I’ll show only a simple one by using DBMS_XMLSTORE.

We will need a simple XML file to import. So I created a folder as “/oracle/xmltest” and a text file named (sample.xml) in this folder. Oracle should be able to access this folder and file, so check OS permissions of them and be sure that they are readable by oracle user.

I entered the followed data into my sample xml file:

&lt;EMPLOYEES&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;1&lt;/NO&gt;
&lt;NAME&gt;Gokhan Atil&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;2&lt;/NO&gt;
&lt;NAME&gt;Ned Flanders&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;3&lt;/NO&gt;
&lt;NAME&gt;Agnes Skinner&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;4&lt;/NO&gt;
&lt;NAME&gt;Milhouse Van Houten&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;/EMPLOYEES&gt;

<NAME>Gokhan Atil</NAME>

</EMPLOYEE>

<NAME>Ned Flanders</NAME>

</EMPLOYEE>

<NAME>Agnes Skinner</NAME>

</EMPLOYEE>

<NAME>Milhouse Van Houten</NAME>

</EMPLOYEE>

</EMPLOYEES>

As you see, our employees have only 2 properties, “no” and “name”. So I’ll create a table to store these properties:

CREATE TABLE empfromxml ( no NUMBER, name VARCHAR2(50 ));

1	CREATE TABLE empfromxml ( no NUMBER, name VARCHAR2(50 ));

January 13, 2009Gokhan Atil16 CommentsOracledata, import, xml