Listing XMLDBpy

import xml.dom.minidom import ODBC.Windows # Replace for your OS as needed import sys import traceback

IMPORTABLE_XML = <?xml version="1.0"?><tabledata><row>

<EMPLOYEE_ID>55</EMPLOYEE_ID><FIRST_NAME>Bertie</FIRST_NAME> <LAST_NAME>Jenkins</LAST_NAME><MANAGER_ID></MANAGER_ID> </row></tabledata>

def ExportXMLFromTable(Cursor):

# We build up a DOM tree programatically, then

# convert the DOM to XML. We never have to process

# the XML string directly (Hooray for DOM!)

DOM=xml.dom.minidom.Document() TableElement=DOM.createElement("tabledata") DOM.appendChild(TableElement) while (1):

DataRow=Cursor.fetchone()

if DataRow==None: break # There is no more data RowElement=DOM.createElement("row") TableElement.appendChild(RowElement) for Index in range(len(Cursor.description)): ColumnName=Cursor.description[Index][0] ColumnElement=DOM.createElement(ColumnName) RowElement.appendChild(ColumnElement) ColumnValue=DataRow[Index] if (ColumnValue):

TextNode=DOM.createTextNode(\

str(DataRow[Index])) ColumnElement.appendChild(TextNode) print DOM.toxml()

def ImportXMLToTable(Cursor,XML,TableName):

# Build up the SQL statement corresponding to the XML

DOM=xml.dom.minidom.parseString(XML) DataRows=DOM.getElementsByTagName("row") for RowElement in DataRows:

InsertSQL="INSERT INTO %s ("%TableName for ChildNode in RowElement.childNodes: if ChildNode.nodeType==\

xml.dom.minidom.Node.ELEMENT_NODE: InsertSQL+="%s,"%ChildNode.tagName InsertSQL=InsertSQL[:-1] # Remove trailing comma InsertSQL+=") values (" for ChildNode in RowElement.childNodes: if ChildNode.nodeType==\

xml.dom.minidom.Node.ELEMENT_NODE: ColumnValue=GetNodeText(ChildNode) InsertSQL+="%s,"%SQLEscape(ColumnValue)

InsertSQL=InsertSQL[:-1] # Remove trailing comma InsertSQL+=")"

Cursor.execute(str(InsertSQL))

def SQLEscape(Value):

return "Null" else:

def GetNodeText(ElementNode):

# Concatenate all text child-nodes into one large string.

# (The normalize() method, available in version 2.1, makes

# this a little easier by conglomerating adjacent

# text nodes for us) NodeText=""

for ChildNode in ElementNode.childNodes:

if ChildNode.nodeType==xml.dom.minidom.Node.TEXT_NODE: NodeText+=ChildNode.data return NodeText if (_name_=="_main_"):

print "Testing XML export..."

# Replace this line with your database connection info:

Conn=ODBC.Windows.connect("AQUA","aqua","aqua") Cursor=Conn.cursor()

Cursor.execute("select * from EMPLOYEE") print ExportXMLFromTable(Cursor)

# Delete employee 55 so that we can import him again

Cursor.execute("DELETE FROM EMPLOYEE WHERE\

EMPLOYEE_ID = 55") print "Testing XML import..."

ImportXMLToTable(Cursor,IMPORTABLE_XML,"EMPLOYEE")

# Remove this line if your database does not have

# transaction support:

Conn.commit()

0 0

Post a comment