simple IronPython example using XML, Oracle and SQL Server

This is an IronPython example of using .NET classes to load a XML document, query it with a simple XPath expression, and then using the results to transfer data from an Oracle to a SQL Server database. 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import clr
clr.AddReference('System.data')
from System.Data.SqlClient import *
clr.AddReference('System.data.OracleClient')
from System.Data.OracleClient import *
clr.AddReference("System.Xml")
from System.Xml import *
import sys

try:
    # connecting to Oracle
    con_oracle = OracleConnection('Data Source=...;User ID=...;Password=...')
    con_oracle.Open()
    # connecing to SQL Server
    con_sql = SqlConnection('Data Source=...;Initial Catalog=...;User ID=...;Password=...')
    con_sql.Open()

    # loading XML
    doc = XmlDocument()
    doc.Load(sys.argv[1])
    # querying the XML
    ids = doc.SelectNodes('//Record/@ID')

    # iterating thru the XPath resultset
    for id in ids:
        id = id.Value

        # extracting info from Oracle
        query = "select TOPICS from OracleTable where ID = '" + oracle_id + "'"
        cmd_oracle = OracleCommand(query,con_oracle)
        reader = cmd_oracle.ExecuteReader()
        topic = ''
        while reader.Read():
            topic = topic + reader['TOPICS'] + '; '
        topic = topic[:-2]
        reader.Close()

        # inserting info in SQL Server
        topic = topic.replace("'","''") # not necessary if using a stored proc
        query = "update SqlTable set topics = '" + topic + "' where id = '" + id + "'"
        cmd_sql = SqlCommand(query,con_sql)
        cmd_sql.ExecuteNonQuery()

        print id, topic

    # closing connections
    con_oracle.Close()
    con_sql.Close()

except Exception,e:
    sys.stderr.write('>>> Error: %s\n' % e)
    sys.exit(1)

Comments

Post a Comment