How to convert data in xml into array or list using tcl script

[A Lim]

The xml is in the format of

<SQL name=”Rpt_SameRptOrder”>
<SQLCode>Rpt_SameRptOrder</SQLCode>
<DataFound>1</DataFound>
<SQLResults>
<ROW>
<COLUMN name=”Variable1″>VariableValue</COLUMN>
<COLUMN name=”Variable2″>VariableValue</COLUMN>
<COLUMN name=”Variable3″>VariableValue</COLUMN>
</ROW></SQLResults>
</SQL>

The section in Rpt_SameRptOrder can run from none (as shown below as sample)

<SQL name=”Rpt_SameRptOrder “>
<SQLCode>Rpt_Type</SQLCode>
<DataFound>0</DataFound>
<SQLResults />
</SQL>

to single or multiple row(s) depending on the data provided by the DB query – sample of multiple rows are as follows:-

<SQL name=”Rpt_SameRptOrder”>
<SQLCode>Rpt_SameRptOrder</SQLCode>
<DataFound>1</DataFound>
<SQLResults>
<ROW>
<COLUMN name=”OrderNo”>ORD0029</COLUMN>
<COLUMN name=”AccNo”>C628</COLUMN>
<COLUMN name=”Code”>Proc1</COLUMN>
</ROW>
<ROW>
<COLUMN name=”OrderNo”>ORD0030</COLUMN>
<COLUMN name=”AccNo”>C629</COLUMN>
<COLUMN name=”Code”>Proc142</COLUMN>
</ROW><ROW>
<COLUMN name=”OrderNo”>ORD0049</COLUMN>
<COLUMN name=”AccNo”>C638</COLUMN>
<COLUMN name=”Code”>Proc133</COLUMN>
</ROW><ROW>
<COLUMN name=”OrderNo”>ORD0089</COLUMN>
<COLUMN name=”AccNo”>C700</COLUMN>
<COLUMN name=”Code”>Proc190</COLUMN>
</ROW></SQLResults>
</SQL>

How do I read the above dynamic Rows into three variables (either in list or array), say OrderNo, AccNo and Code? E.g.
OrderNo contains ORD0029, ORD0030, ORD0049, ORD0089
AccNo contains C628, C629, C638, C700
Code contains Proc1, Proc142, Proc133, Proc190
How do I then reference find the individual values within the variable?

Note that the site is already running live using tcl script for its XML to HL7 configuration so I can’t just use xlate for this – I have to modify the current tcl script. I have other SQL section to read before and after this SQL section. I have tried a couple of methods that I found through google search but I am having issues where the SQL section after this will not be read. Am wondering if anyone could help in resolving this?

Topic

[Charlie Bursell]

Take a look at tclXML package available here: https://tclxml.sourceforge.net/tclxml.html

It is for rudimentary parsing.  If you need more look for he Tcl DOM package.

If simple XML and you have a priori knowledge of the layout it can be done quite nicely with regular expressions.

For some reason, regex scares the hell out of some but it is very powerful if you learn to use it.

 

[A Lim]

Reading of XML and parsing is not the issue – it has been done and works fine. It is the setting up of array to take in the values that is the issue. As long as array is not used, there is no issue in going through the XML file and getting all of the data, but it is n rows into a single variable, hence does not meet the requirement. If I add array, the array works, but the reading of the next XML section fails. I did nothing in the code other than just change to read the value into array variable instead of single variable. So it has to be the way I set up and storing the value in the array variable that is causing this. However, I could not figure out what is the issue.

• This reply was modified 1 month, 1 week ago by A Lim.

[Charlie Bursell]

Sorry I misunderstood the problem.

For the above you need to have three array elements:  OrderNo, AccNo and Code.  Then make each a list . As you parse a value,  lassign it to the proper array element as needed.

Other than this I would need to see your proc.

[Jason Russell]

Would you want to keep them linked? You may want a dictionary where the key is probably order number with the account and code as the dataset, you’d have something similar to:

reportInfo { ORD0029 {C628 PROC1}

ORD0030 {C629 PROC142}

… {…}

}

I’d have to look up the syntax specifically. You can pull the keys via dict keys and process them in a foreach later if you need. I think an array would work as well, but a dictionary will allow you to link an order number to the account/Code, where single variables/arrays would require you to make sure they were in a specific order, and keep that order if removing/adding elements later, assuming you needed that link.

[Robert Kersemakers]

I would use a ‘list of lists’ for this. Something similar to Jason’s answer but a little bit less complicated.

set orderlist {}
foreach column $result {
lappend orderlist

}

You would end up with this orderlist:
{{ORD0029 C628 Proc1} {ORD0030 C628 Proc142} {ORD0049 C638 Proc133} {ORD0089 C700 Proc190}}

You could sort this list and ‘foreach’ through it, column by column. Or you could search for a specific entry. If you want the entry for ‘ORD0049’:

echo [lsearch -exact -inline -index 0 $orderlist “ORD0049”]
ORD0049 C638 Proc133

Zuyderland Medisch Centrum; Heerlen/Sittard; The Netherlands

[A Lim]

So if the list is {{ORD0029 C628 Proc1} {ORD0030 C628 Proc142} {ORD0049 C638 Proc133} {ORD0089 C700 Proc190}} and to find which item in the list has ‘ORD0049’ I would do set ordind [lsearch -index 0 -exact $orderlist “ORD0049”] ? It would give me 3 since it is in the 3rd one?

And if I have this index number (ordind) could I find what value is in another list using that index number? So e.g. the above result index is 3, I now want to find the value with the same index in another list (docdate) say {{Doc1 Date1} {Doc 2 Date2} {Doc 3 Date3} {Doc 4 Date4}} which means I would want {Doc 3 Date3}, does that mean i just need to set Var1 [lindex docdate ordind]  to get the value?

[Charlie Bursell]

I agree with Jason.  More complex data structures are best handled with dictionary.  Arrays are nice if not too complex.

[A Lim]

Sorry, been busy, forgot about this. Looking at this again.
What I did was having 3 variables OrderNo, AccNo and Code and use the following code to read it in

set OrderNo(Ord) 0

set AccNo (Ord) 0

set Code(Ord) 0

foreach line $xmldata {

switch -exact — $xmlTag {

SQLCode {

switch -exact — $xmlData {

…

Rpt_SameRptOrder {set dataType $xmlData} ;

…

}

}

/SQL {set dataType “”}

default {

switch -exact — $dataType {

…

Rpt_SameRptOrder {

          switch -exact — $xmlTag {

              OrderNo {set i OrderNo(Ord)

set OrderNo($i) $xmlData

incr OrderNo(Ord)

              }

              AccNo {set i AccNo (Ord)

   set AccNo ($i) $xmlData

   incr sameaccessNum(Ord)

}

Code{set i Code(Ord)

set Code($i) $xmlData

incr Code(Ord)

}

}

}

Rpt_2ndReqExam {

…

}

}

}

}

…

}

It works fine for this. No issue, and I can see values when I lappend to the debugLog the values of the three variables, but it no longer look into Rpt_2ndReqExam and store the values required in there.

What is dictionary? Would storing into a list resolve the issue have?

[A Lim]

I tried using list, but the same issue – the next section Rpt_2ndReqExam was not read by the script once i added as list for Rpt_SameRptOrder.

 

 

[Author]

Replies