INSTALLATION DOCUMENTS BY RAVI

Sunday, February 16, 2020

Loading xml file data to oracle table using python


Sample xml file (test.xml):

<?xml version="1.0"?>
<data>
    <customer name="Ravi" >
        <email>lravikumarvsp@gmail.com</email>
        <phone>9999999999</phone>
    </customer>
    <customer name="kumar" >
        <email>kumar@gmail.com</email>
    </customer>
    <customer name="lanke" >
        <email>lanke@gmail.com</email>
        <phone>8888888888</phone>
    </customer>
    <customer name="ravikumar" >
        <phone>7777777777</phone>
        <address>
            <street>kukatpally</street>
        </address>
    </customer>
</data>




Python script for parsing xml file data to data frame:

import pandas as pd
import xml.etree.cElementTree as et
#Reading Data from the xml file
parsedXML = et.parse( "test.xml" )                                                                         
dfcols = ['name','email','phone','street']                                                                                
df = pd.DataFrame(columns=dfcols)
def getvalueofnode( node ):
                return node.text if node is not None else None
for node in parsedXML.getroot():                                                                              
                name = node.attrib.get('name')
                email = node.find('email')
                phone = node.find('phone')
                street = node.find('address/street')
                df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)
print (df)                                                                                                                                  










Run the program as below:















Now create a table with column names in the data frame.

CREATE TABLE TEST 
   (                          "NAME" VARCHAR2(200 BYTE),
                               "EMAIL" VARCHAR2(200 BYTE),
                               "PHONE" VARCHAR2(200 BYTE),
                               "STREET" VARCHAR2(200 BYTE)
   )





















Python script for loading xml file to oracle table.

import pandas as pd
import cx_Oracle
import xml.etree.cElementTree as et
#Reading Data from the xml file
parsedXML = et.parse( "test.xml" )                                                                         
dfcols = ['name','email','phone','street']                                
df = pd.DataFrame(columns=dfcols)
def getvalueofnode( node ):
                               return node.text if node is not None else None
for node in parsedXML.getroot():                                                                               
                               name = node.attrib.get('name')
                               email = node.find('email')
                               phone = node.find('phone')
                               street = node.find('address/street')
                               df = df.append( pd.Series([name, getvalueofnode(email), getvalueofnode(phone), getvalueofnode(street)], index=dfcols) ,ignore_index=True)
print (df)                                                                                                                             
#Loading Data into the Database Table
host1 = '172.16.11.32'
port1 = '1521'
sid1 = 'srcdb'
schema1 = 'system'
pwd1 = 'Admin123'
sdsn = cx_Oracle.makedsn(host1, port1, service_name=sid1)    
connect=cx_Oracle.connect(schema1, pwd1, sdsn)                                                        
cursor=connect.cursor()
dataframe_list=df.values.tolist()                                                                                                              # convert the dataframe to list
sql='INSERT INTO TEST values (:1,:2,:3,:4)'
for index,elem in enumerate(dataframe_list):                                                   
                               cursor.execute(sql,dataframe_list[index]) 
                               connect.commit()                                                             













Run the script to load the data to oracle table now















Check the table for data






















No comments:

Post a Comment

SQL query to find whether OdiSendMail is configured or not ODI 12c

In the SQL developer connect to the ODI work repository user (Ex: DEV_ODI_REPO) and run the below query. select SCEN_NAME,TASK_NAME3,DE...