Horrible data structure need to output to XML file so confused

[Gina Maylone]

Good morning all!

This is a followup post to my previous question regarding exporting Access to XML. I've attached a copy of the database and here is the desired output (FROM THE TOSTATE3 TABLE):XML_ToState.zip

<?xml version="1.0" encoding="UTF-8"?>
<EMSDataSet xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance">
<Header>
  <Record>          
<e01>
<e01_01>15-0449</e01_01>
<e01_02>COptions</e01_02>
<e01_03>Northwest Trips</e01_03>
<e01_04>2.1</e01_04>
</e01>
<e02>
<e02_01>99-v007</e02_01>
<e02_04>30</e02_04>
<e02_05>60</e02_05>
<E02_06>105</E02_06>
<E02_07>135</E02_07>
<E02_08>225</E02_08>
<E02_09>290</E02_09>
<E02_10>360</E02_10>
<e02_12>R-2</e02_12>
<E02_20>390</E02_20>
</e02>
<e03>
<E03_01>425</E03_01>
<E03_02>0</E03_02>
</e03>
<e04>
<e04_03>61106110</e04_03>
</e04>
<e05>
<e05_02>03012015000200</e05_02>
<e05_04>03012015000200</e05_04>
<e05_05>03012015000200</e05_05>
<e05_06>03012015000200</e05_06>
<e05_07> </e05_07>
<e05_09> </e05_09>
<e05_10> </e05_10>
<e05_11>03012015001600</e05_11>
<e05_13>03012015001600</e05_13>
</e05>
<e06>
<E06_08></E06_08>
<E06_11>-10</E06_11>
<E06_12>-10</E06_12>
<E06_13>-10</E06_13>
<E06_16>  </E06_16>
</e06>
    </Record>
</Header>
</EMSDataSet>

I have tried many things, and cannot for the life of me figure out how to add the start and end tags between groups (ie. <e01> and </e01) - I can export the xml fine - but of course the extra tags do not show up. Any thoughts? NOTE: I did NOT create this db, I inherited it. The XML needs to be uploaded to a state database monthly. I thank you in advance and wish you all a VERY HAPPY FRIDAY!

[June7]

Are you using the export wizard?

An HTML file is just a text file. Code can construct text file - line by line. This is what you will have to do if you want to do something the wizard cannot accomplish.

[Gina Maylone]

That's what I was afraid of. No the wizard doesn't add the tags that are needed. I'm trying to figure out right now how to write it out to a file with the extra tags. Thanks for replying again June!

[June7]

Will need to open recordset of data and loop through records and write to text file. Review http://www.tek-tips.com/faqs.cfm?fid=3767

[ssanfu]

I took a different approach than June. Since you have the code to create the XML file, I edit that file.

If the 2nd line of the XML file does not begin with "<dataroot", you will have to edit the code.

So the code saves the XML file, adds the tags by reading the original XML file, writing to a 2nd XML file (with a _1) suffix.
Then the code deletes the original XML file and renames the new file to the old file name.

The code executes with no errors on my computer. Hopefully it does on you computer also.



You might also consider converting the mdb to an accdb format dB.

[Gina Maylone]

WOW! THANK YOU STEVE!! That's amazing! I looked through the code and it is SO over my head. Thanks again!

Gina

[ssanfu]

You're welcome.... but it's not that hard.

Start by opening the two files for input and output.
Read/write lines until it is time to add the headers
Then there is a Do Loop until end of input file
Determine when to write (add) the opening and closing tags using If statements to know exactly when to insert lines
Write the lines that already exist
Then write the closing commands.
Delete the original xml file
Rename the new file to the original xml filename

Easy as falling off a log .... (pun intended)

[Gina Maylone]

:d:d:d

[Gina Maylone]

Steve,

How do I change <dataroot xmlnsd="urn:schemas-microsoft-comfficedata" generated="2015-11-23T15:42:46"> to <EMSDataSet xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance">? Do I just need to do a replace within the text file?

[ssanfu]

Tried to download your accdb file, but it was corrupt???
I imported the tables/code from the mdb file.

Try

    'read lines
    Line Input #RF, TextLine   '<?xml version="1.0" encoding="UTF-8"?>
    Print #WF, TextLine
    Line Input #RF, TextLine   '<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2015-11-20T18:29:45">
    '    Print #WF, TextLine
    Print #WF, "<EMSDataSet xmlns:xsd=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsi= ""http://www.w3.org/2001/XMLSchema-instance"" > """

Then at the bottom of the code, change

    'print closing record tag
    Print #WF, "</Record>"
    'print closing header tag
    Print #WF, "</Header>"
    'print closing dataroot tag
'    Print #WF, TextLine            ' "</dataroot >"
    Print #WF, "</EMSDataSet>"

See attached zip

[Gina Maylone]

You never cease to amaze me Steve! If you're in Minnesota on Thursday please stop by for Thanksgiving dinner! THANK YOU!!!