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
}
end
local assignedPersonName = Msg.ClinicalDocument.informant.assignedEntity.assignedPerson.name.given[1]:nodeValue()
local assignedPersonfamilyName = Msg.ClinicalDocument.informant.assignedEntity.assignedPerson.name.family[1]:nodeValue()

local SqlInsert =
[[
INSERT INTO patient
(
LastName,
GivenName

)
VALUES
(
]]..
“‘”..assignedPersonName..”‘,”..
“‘”..assignedPersonfamilyName..”‘”..
‘\n)’

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
print(r)

for i=1, #r do
print(i)

local SqlInsert2 =

–require ‘node’

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

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

“‘”..observationcode..”‘,”..
“‘”..observationstatuscode..”‘,”..
“‘”..observationcodesys..”‘,”..
“‘”..observationcodesysname..”‘”..
‘\n)’

trace(SqlInsert2)

— (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}
end

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

Any help on this will be greatly appreciated.

Thanks,
Reshma

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. ◊ http://www.hicgrp.com

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.10.20.1.11″/>
<templateId root=”1.3.6.1.4.1.19376.1.5.3.1.4.6″/>
<templateId root=”1.3.6.1.4.1.19376.1.5.3.1.4.5″/>
<templateId root=”2.16.840.1.113883.10.20.1.28″/>
<id root=”2.16.840.1.113883.4.391.30″ extension=”4205751.Info Not Available”/>

<statusCode code="completed"/>
<effectiveTime>
<low value="20140307"/>
<high value="20140307"/>
</effectiveTime>
<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>

<name>N.K.D.A.</name>
</playingEntity>
</participantRole>
</participant>
<entryRelationship typeCode="MFST" inversionInd="true">
<observation classCode="OBS" moodCode="EVN">
<templateId root="2.16.840.1.113883.10.20.1.54"/>

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

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

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'
      end
   end
   return obStr
end

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. ◊ http://www.hicgrp.com

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

Thanks again!

Thanks,
Reshma