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>


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):


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):


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+=")"


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


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


# Remove this line if your database does not have

# transaction support:


0 0

Post a comment