Import XML into Database Table - XML Has Some Multiple Values Per Field

LawnyToast

New Member
Below is the XML. In the fields next_op_mach and next_op_wc. There are multiple values for the one field. I would like to get the fields to append or be added together. Progress seems to just overwrite the data in the field instead of adding to or appending the field. I added extra spacing on the problem area for clarity.

Here is the xml:
Code:
<row ID="COMPPD\70355-054\70355-054-BA\40" >
    <id>COMPPD\70355-054\70355-054-BA\40</id>
    <finish>01 APR 2021 12:42</finish>
    <finish_date>04-01-21</finish_date>
    <job>70355-054</job>
    <length_sched>0.666666666666667</length_sched>
        <next_op_mach>A10POLS1-M1</next_op_mach>
        <next_op_mach>360</next_op_mach>
        <next_op_wc>A10POLS1</next_op_wc>
        <next_op_wc>A2LASR1</next_op_wc>
    <operation>40</operation>
    <part_no>60813</part_no>
    <prev_op_mach></prev_op_mach>
    <prev_op_wc></prev_op_wc>
    <quantity>1</quantity>
    <sched_mach>331</sched_mach>
    <sched_wc>A1CMIL1</sched_wc>
    <start>01 APR 2021 12:02</start>
    <start_date>04-01-21</start_date>
    <status_text>Scheduled</status_text>
    <work_order>70355-054-BA</work_order>
    <wo_desc>Base Insert</wo_desc>
    <wo_due>15 MAR 2021 00:00</wo_due>
    <wo_priority>50</wo_priority>
</row>

Here is the dream record, with only one value in such fields.

Code:
<row ID="COMPPD\70355-054\70355-054-BA\50" >
    <id>COMPPD\70355-054\70355-054-BA\50</id>
    <finish>01 APR 2021 14:05</finish>
    <finish_date>04-01-21</finish_date>
    <job>70355-054</job>
    <length_sched>1.38333333333333</length_sched>
    <next_op_mach>360</next_op_mach>
    <next_op_wc>A2LASR1</next_op_wc>
    <operation>50</operation>
    <part_no>60813</part_no>
    <prev_op_mach>331</prev_op_mach>
    <prev_op_wc>A1CMIL1</prev_op_wc>
    <quantity>1</quantity>
    <sched_mach>A10POLS1-M1</sched_mach>
    <sched_wc>A10POLS1</sched_wc>
    <start>01 APR 2021 12:42</start>
    <start_date>04-01-21</start_date>
    <status_text>Scheduled</status_text>
    <work_order>70355-054-BA</work_order>
    <wo_desc>Base Insert</wo_desc>
    <wo_due>15 MAR 2021 00:00</wo_due>
    <wo_priority>50</wo_priority>
</row>

Using information and assistance from these articles Here and Here. I have successfully read this XML document and created a temp-table. Ready to write to a database table. However, I am missing the initial values of the repeated fields (as progress is just overwriting them with the latter value). Do you know of a way to have these values add up if they are repeated?

Here is my code:

Code:
def temp-table row
   field id                  as char
   field finish              as char
   field finish_date         as char
   field job                 as char
   field length_sched        as char
   field next_op_mach        as char
   field next_op_wc          as char
   field operation           as char
   field part_no             as char
   field prev_op_mach        as char
   field prev_op_wc          as char
   field quantity            as char
   field sched_mach          as char
   field sched_wc            as char
   field start               as char
   field start_date          as char
   field status_text         as char
   field work_order          as char
   field wo_desc             as char
   field wo_due              as char
   field wo_priority         as char.
 
def var v-sourcetype         as char no-undo.
def var v-read-xml           as char no-undo.
def var v-read-xml-path      as char no-undo.
def var v-readmode           as char no-undo.
def var v-schemapath         as char no-undo.
def var v-override-def-map   as log  no-undo.
def var v-field-type-map     as char no-undo.
def var v-verify-schema-mode as char no-undo.
def var v-test_xml           as char no-undo.
def var cOutputFile           as char no-undo.

assign v-sourcetype         = "FILE"
       v-read-xml           = "operation.xml"
       v-read-xml-path      = "F:\FLX\" + v-read-xml
       v-readmode           = "EMPTY"
       v-schemapath         = ?
       v-override-def-map   = ?
       v-field-type-map     = ?
       v-verify-schema-mode = ?.

TEMP-TABLE row:READ-XML(v-sourcetype, v-read-xml-path , v-readmode, 
                            v-schemapath, v-override-def-map, 
                            v-field-type-map, v-verify-schema-mode).

Perhaps something could be altered in my read type definitions?
 

Stefan

Well-Known Member
As mentioned in one of your references, you need an extent.

the.xml:

Code:
<row>
   <one>foo</one>
   <one>bar</one>
</row>

the.p:

Code:
define temp-table ttrows serialize-name 'row'
   field one as char extent 2
   .

temp-table ttrows:read-xml( "file", "the.xml", ?, ?, ? ).

define buffer burow for ttrows.

for each burow:
   display
      burow.one[1]
      burow.one[2]
      .
end.

 

LawnyToast

New Member
I see what you mean. I suppose this would be the perfect case for EXTENT! Thank you very much for referring back to that, and thank you for the quick assist. I will run tests and post results before closing the thread.
 

LawnyToast

New Member
That worked perfectly.

In case someone in the future has these exact questions. This article Here details how to select the different "Extent" of a variable with indexing.

Code:
export delimiter "," id finish finish_date job length_sched next_op_mach[1] next_op_mach[2] next_op_wc[1] next_op_wc[2] operation part_no prev_op_mach[1] prev_op_mach[2] prev_op_wc[1] prev_op_wc[2] quantity sched_mach sched_wc start start_date status_text work_order wo_desc wo_due wo_priority.
 
Top