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






















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...