Using the translator to parse XML files into a database.

This was a question asked by Dick on our linked in forum. I thought I would try posting it here and see what this forum is like to use.

Jeffrey Lewis gave these links:

A quick example for parsing xml is here:

http://wiki.interfaceware.com/634.html

Some more details are here:

http://wiki.interfaceware.com/418.html

For inserting into table you can use:

http://wiki.interfaceware.com/336.html.

Jeff wrote:

Also if using 5.5 should be able to try this:

Channel= Source=LLP, Filter=Yes, Destination=To Channel
In filter copy code below:
Create database from demo.vmd and call it test_demo.sqlite
http://wiki.interfaceware.com/343.html

function main(Data)
  -- database
  DB = db.connect{api=db.SQLITE, name='test_demo.sqlite'}

  -- XML
  local XML = [[12345JeffMcLaughlin]]

  local X = xml.parse{data=XML}

  -- tables you will have to
  local T = db.tables{vmd='example/demo.vmd', name='ADT'}

  -- data
  T.patient[1].Id = X.Patient.Id[1] 
  T.patient[1].LastName = X.Patient.Name.Last[1]
  print(T)

  -- the data into the database table 
  DB:merge{data=T, live=true}  
  DB:query{sql='Select * from patient'}
end

There are quite a few little helpful tips that you can have with using the translator to take XML data from files and put them into a database. Next couple of days are busy for me but there are a number of tips I think are helpful.

Starting off with the file piece. You could use the From File component. Personally I’d recommend just using a From translator component and reading the files in using Lua – it’s more flexible. This is a good example of reading in a file:

http://wiki.interfaceware.com/374.html

Then another useful tip is:

http://wiki.interfaceware.com/638.html

Which is a helpful utility function to grab text enclosed in a tag.

If you are not familiar with the translator then doing one of the introductory tutorials using it would be handy.

Thank you for all your help. I was able to get started using Jeff’s example above and have IGUANA write the sample XML data to a SQL Server DB on another server. Next up is to setup the Translator to parse some XML files I have and extract the data and send it over to the DB server.

Very good!