This topic contains 5 replies, has 2 voices, and was last updated by  Eliot Muir 9 years, 10 months ago.

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:

    Some more details are here:

    For inserting into table you can use:

    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

    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]
      -- the data into the database table 
      DB:merge{data=T, live=true}  
      DB:query{sql='Select * from patient'}

    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:

    Then another useful tip is:

    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!


You must be logged in to reply to this topic.