This topic contains 4 replies, has 2 voices, and was last updated by  hegder03 7 years, 4 months ago.

Loop through CCD xml message and process to DB

  • Hi,
    I am very new to lua scripting.I want to loop through a repeating xml tag(Eg: Observation) and process this repeating message into Oracle DB table.
    I am able to loop through, but its picking the same tag value ‘n’ times i.e,the loop counter is not incremented.
    Below is the code I am using:

    function main(Data)
    — Parse the XML message
    –local xml =[[
    local Msg = xml.parse(Data)

    — (1) connect to the database
    if not Conn or not Conn:check() then
    Conn = db.connect{
    api = db.ORACLE_ODBC,
    name = ‘Oracle_Local’,
    user = ‘HR’,
    password = ‘Welcome1234$’,
    live = true
    local assignedPersonName =[1]:nodeValue()
    local assignedPersonfamilyName =[1]:nodeValue()

    local SqlInsert =
    INSERT INTO patient


    Conn:execute{sql=SqlInsert, live=true}


    local observationcode = Msg.ClinicalDocument.component.structuredBody.component.section.entry.act.entryRelationship.observation.code.code
    local observationstatuscode =Msg.ClinicalDocument.component.structuredBody.component.section.entry.act.entryRelationship.observation.statusCode[1]:nodeValue()
    local observationcodesysname= Msg.ClinicalDocument.component.structuredBody.component.section.entry.act.entryRelationship.observation.code.codeSystemName
    local observationcodesys=Msg.ClinicalDocument.component.structuredBody.component.section.entry.act.entryRelationship.observation.code.codeSystem

    local r = Msg.ClinicalDocument.component.structuredBody.component.section.entry.act.entryRelationship.observation

    for i=1, #r do

    local SqlInsert2 =

    –require ‘node’

    ” INSERT INTO obsv”..
    “\n (“..
    “\n code,”..
    “\n statuscode,”..
    “\n codesystem,”..
    “\n codingsystemname”..

    “\n )”..
    “\n VALUES”..
    “\n (“..



    — (3) Insert data into database

    — Note: This insert statement will fail and raise
    — an error for any duplicate patient record
    –Conn:execute{sql=’DELETE FROM patient’ ,live=true}

    Conn:execute{sql=SqlInsert2, live=true}

    Conn:query(‘select * from patient’)
    Conn:query(‘select * from obsv’)

    Any help on this will be greatly appreciated.


    Look at the child() and childCount() methods to work with repeating collections. The iterator is actually incrementing, or the script would never stop. The problem is that you’re not selecting a specific iteration of the observation collection, so you’re getting the same children every time.

    Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊

    Thanks Jeff,

    Below is the sample message that I am using.I think observation collection is repeating.Please advice.

    <entryRelationship typeCode=”SUBJ” inversionInd=”false”>
    <observation classCode=”OBS” moodCode=”EVN”>
    <templateId root=”2.16.840.1.113883.″/>
    <templateId root=”″/>
    <templateId root=”″/>
    <templateId root=”2.16.840.1.113883.″/>
    <id root=”2.16.840.1.113883.4.391.30″ extension=”4205751.Info Not Available”/>

    <statusCode code="completed"/>
    <low value="20140307"/>
    <high value="20140307"/>
    <value xsi:type="CD" code="420134006" codeSystem="2.16.840.1.113883.6.96" codeSystemName="SNOMED CT" displayName="Adverse Reaction"/>
    <participant typeCode="CSM">
    <participantRole classCode="MANU">
    <playingEntity classCode="MMAT">

    <originalText><reference value="#allergy1"/></originalText>

    <entryRelationship typeCode="MFST" inversionInd="true">
    <observation classCode="OBS" moodCode="EVN">
    <templateId root="2.16.840.1.113883."/>

    <statusCode code="completed"/>
    <value xsi:type="CD" code="NA" codeSystem="2.16.840.1.113883.6.96" codeSystemName="SNOMED CT" displayName="Info Not Available"/>
    <entryRelationship typeCode="REFR">
    <observation classCode="OBS" moodCode="EVN">
    <templateId root="2.16.840.1.113883."/>

    <statusCode code="completed"/>
    <value xsi:type="CE" code="55561003" codeSystem="2.16.840.1.113883.6.96" codeSystemName="SNOMED CT" displayName="Active"/>

    Here’s some code I hacked together quickly to demonstrate how you might iterate through the repeating collections in your sample XML:

    function getObs(Data)
       local xDoc = xml.parse{data=Data}
       local obStr = ''
       for i=1,xDoc.entryRelationship.observation:childCount("entryRelationship") do
          local er = xDoc.entryRelationship.observation:child("entryRelationship",i)
          for j=1,er:childCount("observation") do
             local ob = er:child("observation",j)
             obStr = obStr .. '\t' .. er.typeCode .. '\t' .. ob.classCode .. '\t' .. ob.moodCode .. '\t' .. ob.value.displayName .. '\n'
       return obStr

    It simply takes the values from each observation and builds a string that lists them in tabular form.

    Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊

    Thanks Jeff..
    This is really helpful..I am now able to iterate and loop through the messages.

    Thanks again!


You must be logged in to reply to this topic.