[Download-List] XML and mutli-value fields

Davis Kriss P kpdavis at ilstu.edu
Mon Dec 15 15:40:59 EST 2003


Here is a DOWNLOAD paragraph I am running on our GLS.2004 file:

001: PA

002: SELECT GLS.2004

003: SAVE.LIST DOWNLOAD.GLS.2004

004: GET.LIST DOWNLOAD.GLS.2004

005: DOWNLOAD GLS.2004 \

006: @ID \

007: OPEN.BAL \

008: DEBITS.YTD \

009: CREDITS.YTD \

010: MDEBITS NUM.VALUES ALL \

011: MCREDITS NUM.VALUES ALL \

012: ACCOUNT.ID \

013: ACTIVITY.SEQ \

014: CLOSE.DEBITS  \

015: CLOSE.CREDITS \

016: GLS.FSLVL \

017: GLS.FYR.ADD.OPERATOR \

018: GLS.FYR.ADD.DATE \

019: GLS.FYR.CHANGE.OPERATOR \

020: GLS.FYR.CHANGE.DATE \

021: B.ORIG.APPROVED.AMT \

022: B.APPR.DEBITS.YTD \

023: B.APPR.CREDITS.YTD \

024: B.CONT.DEBITS.YTD \

025: B.CONT.CREDITS.YTD \

026: B.ALOC.DEBITS.YTD \

027: B.ALOC.CREDITS.YTD\

028: E.OPEN.BAL \

029: ENCUMBRANCES.YTD \

030: ENCUMBRANCES.RELIEVED.YTD \

031: ENCUMBRANCES.RELIEVED.YTD.CONV \

032: GLS.BUDGET.LINKAGE \

033: GLS.POOLED.TYPE \

034: GLS.ESTIMATED.OPEN.BAL \

035: FORMAT XML   \

036: PROGRESS.INTERVAL 1000 \

037: PRINT.LAYOUT \

038: FILE DOWNLOAD.TEMP DOWNLOAD.GLS.2004 OVERWRITING

039: DELETE.LIST DOWNLOAD.GLS.2004   

You will note at lines 10 and 11 I am asking for MDEBITS and MCREDITS a
multi-value field and asking for the values to be expanded (at least I
think that is what I am asking for).  Also, at line 35, I am asking for
XML output.  If you want to run this, you may need to change
DOWNLOAD.TEMP (line 38) to a directory you have in your VOC.  I created
DOWNLOAD.TEMP and pointed it to /tmp to keep from filling up the file
system that has COLLIVE in it.

This is actually working pretty well.  I have run into two
situations/questions.  The fields and data import quite nicely into
ACCESS 2000.  But they import even better into ACCESS 2003.

1.  When I produce this file and bring import the XML into MS ACCESS, MS
ACCESS recognizes the M.ACTUALS "structure" and builds a separate table
MACTUALS for fields MDEBITS and MCREDITS.  The rest of the single value
fields ends up in the "main" table.  The one problem I have is that
there is no shared key field between these two ACCESS tables.  I cannot
relate the entries in the main/single value table to the entries in the
M.ACTUALS table.   I do not see how to get the key to replicate into the
M.ACTUALS.  May not be a way...  I think having the @ID replicated into
the MACTUALS would do the trick.

2.  The other problem is that all the data fields (text, dates, dollar
amounts) come into MS ACCESS as TEXT fields by default.  It appears the
XML does not provide any clues to he import process that certain fields
are numbers or dates.  Again, this can be adjusted afterword.  I figure
if I define the table in ACCESS first and import into it, the data will
come in with the field types already present.

This along with the TOXML paramenter that has been added to the LIST
Unidata command has added some interesting data movement options.  For
example...

LIST DICT GL.ACCTS TO /home/kpdavis/glaccts.xml TOXML 

puts out an XML file of the dictionary of GL.ACCTS.  This can be brought
into EXCEL for documentation, etc.

Just thought I would share this with everyone in case they have been
playing with these new features.  I would be very interested in any info
on the above 2 "problems".  But even if these do not get resolved, the
XML features in DOWNLOAD 7.02 will be VERY useful.  Thanks Dave for
these improvemets!!

Kriss




-------------------------
Kriss Davis, CCP
Illinois State University
kpdavis at ilstu.edu
-------------------------



More information about the download-list mailing list