​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Parse an XML file and insert the data into an Oracle database using VB6


In this article I'm going to show how to parse an XML file using the Document Object Model (DOM) and then insert the data into an Oracle database using ODBC. I assume you have a DSN already setup for your Oracle database and that the database contains a stock table containing the columns stock_name, price, currency and date. We're going to parse the sample XML file below and insert the records into our stock database table. Although a very simple example it should give you enough knowledge to go on and do some more complex tasks. Before you do any coding you need to obtain the latest MSMXL object from microsoft. Once installed this gives you access to all the DOM properties and methods required when you reference it from VB. Once an XML document is loaded DOM effectively gives you a tree-like structured version of the document enabling you to traverse it node at a time or do a whole host of other complex regular expression type matching on the nodes.


The sample XML file


<?xml version="1.0" encoding="ISO8859-1" ?>
<DATABASE>
<STOCK>
<NAME>STOCK A</NAME>
<PRICE>10.90</PRICE>
<CURRENCY>USD</CURRENCY>
<DATE>20040531</DATE>
</STOCK>
<STOCK>
<NAME>STOCK B</NAME>
<PRICE>1.07</PRICE>
<CURRENCY>EUR</CURRENCY>
<DATE>20040531</DATE>
</STOCK>
<STOCK>
<NAME>STOCK A</NAME>
<PRICE>99.12</PRICE>
<CURRENCY>GBP</CURRENCY>
<DATE>20040531</DATE>
</STOCK>
<STOCK>
<NAME>STOCK C</NAME>
<PRICE>100.08</PRICE>
<CURRENCY>YEN</CURRENCY>
<DATE>20040531</DATE>
</STOCK>
<STOCK>
<NAME>STOCK D</NAME>
<PRICE>777.70</PRICE>
<CURRENCY>YEN</CURRENCY>
<DATE>20040531</DATE>
</STOCK>
</DATABASE>


The VB6 program to parse and insert into Oracle


Dim xdoc As MSXML.DOMDocument
Dim xnl As MSXML.IXMLDOMNodeList
Dim db As Connection

' Routine to populate an Oracle table with data
' from an XML file.
'

Public Sub MAIN()

Set xdoc = New MSXML.DOMDocument

' Wait until whole document is loaded before parsing it
xdoc.async = False

'Process stock file
If xdoc.Load("c:\stock.xml") Then

process_stocks

Else

' Document failed to open
MsgBox ("Error opening input entity document")

End If

Set xdoc = Nothing

end sub


' Process stock data
Sub process_stocks()

Dim xnode As MSXML.IXMLDOMNode
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=ora;uid=myuser;pwd=mypass;"

qte = Chr(39)

' Oracle dates are not usually in the the same format as standard XML dates
' so force them to use the same format

db.Execute "alter session set nls_date_format='yyyy-mm-dd'"

' For each stock node -
' construct an INSERT (cols...) values(....) statement from the data
' contained within the node/sub-nodes. The replace function used below
' deals with strings containing the single quote character which causes
' Oracle to barf. Replace with two single quotes to fix.

For Each xnode In xdoc.selectNodes("//stock")

col_list = ""
val_list = ""

' for each element within the stock node
For z = 1 To xnode.childNodes.length

col_list = col_list & xnode.childNodes.Item(z - 1).nodeName & ","
xnode.childNodes.Item(z - 1).Text = Replace(xnode.childNodes.Item(z - 1).Text, "'", "''")
val_list = val_list & qte & xnode.childNodes.Item(z - 1).Text & qte & ","

Next z

Mid(col_list, Len(col_list), 1) = ")"
col_list = "insert into stock(" & col_list
Mid(val_list, Len(val_list), 1) = ")"
val_list = "values(" & val_list
sqlstmnt = col_list & val_list

'insert into the database
db.Execute sqlstmnt

Next xnode

db.Close
Set db = Nothing

End Sub