Exporting in xml using a module (1 Viewer)

Mat1994 · Jan 31, 2019

Hi All,

I've created a access database using access 2016. This db is an airport database. It regroups the data that will be used by two differents software.

I'm here now because I need to export my db in a specific way and in a xml file. But I don't know how to do this.

The result I need to get after export is this :

Code:

<Database>
    <AirportList>
       <Airport>
           <Name>BOSTON(LOGAN).MA</Name>
           <State>MASSACHUSETTS,US</State>
           <City>BOSTON</City>
           <Icao>KBOS</Icao>
           <Iata>BOS</Iata>
           <Latitude>42210000N</Latitude>
           <Longitude>-71000000W</Longitude>
           <Elevation>6</Elevation>
           <MagneticVariation>-1600000W</MagneticVariation>
           <Comments>Hours of operation:</Comments>
           <LastUpdate />
               <RunwayList>
                  <Runway>
                     <MagneticHeading />
                     <MagneticHeadingDate />
                     <Strength />
                     <MaxLength />
                     <Width>46</Width>
                     <Shoulder />
                     <Comments />
                     <LastUpdate />
                     <QfuList>
                        <Qfu>
                          <Name>04R</Name>
                          <Ident />
                          <Applicability> </Applicability>
                          <Asda>3050</Asda>
                          <Lda>2697</Lda>
                          <Toda>3050</Toda>
                          <Tora>3050</Tora>

This is only a small part of the data I need to export.
At first I thought of a query I could export but I got stock because I didn't know how to deal with the fact sometimes two differents data have the same name and how to deal with the tags :
<AiportList>
<RunwayList>
<Runway>
<QfuList>
<Qfu>
...

Then I thought of creating a module (a public function) where I could structure the code like I want. But I don't know how to do it. I don't really like the idea of a query because I don't think it's usefull to store the data in it as the data is already stored in the tables, you can't use tags and you can't name two differents data the same way. But I'm maybe wrong.

PS: I forgot to mention that the export will occur after the user clicks on a button in a form.

Thank you for your help,
Mat

theDBguy · Jan 31, 2019

Hi Mat. Just curious, have you tried the Import/Export XML methods?

sxschech · Feb 1, 2019

In post #4 of this link, is an example of vba to generate an xml file.
https://www.access-programmers.co.uk/forums/showthread.php?t=295200

You pretty much start with your existing xml output and then add a bunch of quotes around it and change the values to the recordset fields and variables.

Mat1994 · Feb 3, 2019

theDBguy
Yes, but I quiclky end up in a dead end because I don't know how to add a tag and I need to have two fields with the same name (and a query won't let me do that).

sxschech
Thank you for your code. I'm still a beginner with access and coding, so I don't fully understand your code.

What does this do? What's the purpose of .cboProductCode?

Code:

 If IsNull(Me.cboProductCode) Then
        MsgBox "Please choose a product code and then export to xml", vbOKOnly, "Missing Product Code"
        GoTo Exitsub
    End If

Is this how you let the user chose where he wants to save his xml file?

Code:

Me.lblExportXMLNote.Visible = True
    xmlFile = DLookup("FileLocation", "tblFileLocations", "FileType='xml Files'") & Me.cboProductCode & ".xml"
    If Dir(xmlFile) <> "" Then
        yesno = MsgBox("File exists.  Overwrite / Replace / New Location?", vbYesNoCancel, "File Exists")
        If yesno = vbNo Then
            GoTo Exitsub
        ElseIf yesno = vbCancel Then
            stFolder = selectFolder
            xmlFile = stFolder & "\" & Me.cboProductCode & ".xml"
        End If
    End If

In Set rs = db.OpenRecordset("SELECT * FROM xmlExam where ProductCode='" & Me.cboProductCode & "'")
Is xmlExam a query where your put the data you want to export?

What's the use of Print #1? Is it a way of adding a tag?

For my xml file, I thought I could write the code line by line, so I can rename fields, and let the code go get the data stored directly in the table. It will also let me insert lines such as <LastUpdate />. LastUpdate isn't part of my db because it doens't have any data but the software that will read the xml file needs to read it.
In the past, I tried doing a query. But the query create a new record for each obstacles assigned to a runway. I'd like to avoid that, that's why I think writing a code is the best option.
So my questions now, as a consequences of the questions about your code, are :
- How can I save the export and create a new file with named with the date and time.
- How can I write a code to display the xml correctly? For example, in my db there is a field named "AirportName" which corresponds to the xml field "name". It would be like <name>me.AirportName</name>

If I'm not clear enough, let me know, I'll try and explain thing in a better way,
Thank you for your help,
Mat

sxschech · Feb 4, 2019

To answer your questions...
My form was set up so that if the user clicked the create xml button and didn't choose a product code it would notify them to pick one. The query/recordset was set up to only output data for one product code. If you don't need to "filter" for a specific item, then this would not be necessary. The other part is checking to see if an xml file was already created and ask the user if they want to replace it. This could be modified to allow user where to save if the location is not fixed (choose different locations).

The print statements are how you save data to the text (xml) file following the layout pattern. Whatever is on the print statement line will be saved as it is displayed (variables/recordset fields will show the value assigned to it for that record).

The recordset would be either the query containing all the data, or an sql statement that selects the data you need to produce the output. Yes, the dates or other items not part of the query can be inserted into the print statements.

I can probably help some more during the week.

Mat1994 · Feb 4, 2019

Thanks,

What would I have to change in your code to have an automatic save, where the name of the file would be the date+time?

For the print statement. Would it be a good idea if I used the print #1, to rename my field and display the right data? For example, for the name of an airport, writing 'Print #1, " <name> me.AirportName <\name>
This way I can place my taggs at the right place and display the data in the right order, because...

Concerning the recordset:
The structure of the xml must be :

<Airport>
(information about the airport)
<Runway1>
(information about the runway)
<Obstacles1>
(list and information about the obsatcle)
<Obstacles2>
(list and information about the obsatcle)
<Runway2>
<Obstacles1>
And so on...

Thank you for your help, looking hearing form, you this weekend,
Mat

sxschech · Feb 7, 2019

Any chance you could provide a stripped down db as well as a file example of the layout of your xml file or as much as you think I would need to get you started? Also, when you mention "automatic save", I assume that means you want the file to be saved in a specific folder without user intervention? Is "the name of the file would be the date+time", is it like this? c:\XMLfolder\20190208_1410.xml or how should it look? Will the xml file be for one item/group or everything in a table/query? You mentioned me.AirportName, does that mean all of the data that should populate the xml file comes from a form or is it a combination of form and query?

Mat1994 · Feb 8, 2019

Also, when you mention "automatic save", I assume that means you want the file to be saved in a specific folder without user intervention?

Yes, when the user clicks on the export button, I would like the xml to save itselve directly in a folder I chose (I:\FlightOps\Perfeng\wordperf\NEW Airport Database Project\06-Export)

Is "the name of the file would be the date+time", is it like this? c:\XMLfolder\20190208_1410.xml or how should it look?

Yes, I would also like the save the xml as the date+time. This will let me keep a archive of the xml.
Yes, c:\XMLfolder\20190208_1410.xml is fine

Will the xml file be for one item/group or everything in a table/query?

I'm not sure I understand the question. The xml file will be used be the engineers to run calculation in another software they use.

You mentioned me.AirportName, does that mean all of the data that should populate the xml file comes from a form or is it a combination of form and query?

This is the part where I'm not to sure of the method I need apply to get the export. So in my db, I have the tables that stores the data. I have a form that lets the user, view, edit and create a new record. Even if I need to create a query (I've tried), I don't know how to structure it.

I've attached my access database, and a example of a xml file I need to get. The Access db is the first db I've created and my first experience with access, please don't judge me too quickly when you open it. The xml is a example of what the calculation software needs to read. The access database has one airport with two runways and a second airport with one runway.
I will also try an explain part of my database and the xml example.

Concerning the database :
First of all, my database is a airport database. The engineers have asked me to create a new database that regroups the data of Airbus and of Boeing. They are two aircraft company, that use two different databases.
One is a Access db but not the other one. Both have mostly the same data, but name the data differently.
So in the tables I created, I combined the data, changed name (when the names of the data wasn't the same in Airbus and Boeing).
In the realtionship, one airport can have many runways, and one runway can have many obstacles and many procedure.
I've also created a form (frm_Userinterface) where the user will be able to add a new record, or edit and save a record already created. Most of the data in the form are bound to the tables. I also add a button (Export to airbus) where, on click the data will be exported in xml. However this export, will need to rename certain field accordingly to Airbus.
The db I created also has empty field but they may be used in the futur but not now. You will aslo notice a field "ActypeRunway". I know I was told that a field cannot have multiple value but for the moment the db works. This field (ActypeRunway) is a liste of aircraft you can apply to a runway (they will only be a few). It also corresponds to the field "Applicability" of the xml file.

Corcerning the xml :
The xml will be read by a Airbus software. Therefore, all the field you will see in the example attached have to be in the access xml export and need the same name.
In the example attached, you will also see some field without data. Some of this data is empty because the engineer decided to. But some fields are empty because they are useless, therefore I decided to not create a field in the tables for them in the access database (e.g <lastUpdate>). Then again the exported xml need to have exacty the same lines as the attached xml example.

Note : The examples in the database are invented, they are not real data.

Please let me know if you have any question,
Thank you for your help,
Mat

sxschech · Feb 8, 2019

I took a look at the form.
Is XML to be created when user clicks on the button labeled Export to Airbus?

Will the export be for what ever record is being displayed? When I open the form it shows 3 records.

Will the xml file be for one item/group or everything in a table/query?

Given your sample data, means are you exporting data for all three records or only one specific record. Either 1 or 2 or 3?

Mat1994 · Feb 11, 2019

Is XML to be created when user clicks on the button labeled Export to Airbus?

Yes.

Will the export be for what ever record is being displayed? When I open the form it shows 3 records.

No, I would like to export all the data. So the xml file will have all the data about all the airports displayed one after the other with the same structure as the xml I shared with you.
So I guess what would be good is : After the user click on the "export to Airbus " button. The code saves the record displayed in the form and then exports the data.

Thank you for your help,
Mat

sxschech · Feb 12, 2019

Thanks for the clarification. I may time tonight or Thursday to give it a go. Will reply when I have something, sorry if there may be a delay in getting back to you.

Mat1994 · Feb 13, 2019

Thank you for your help.

The last clarification I can give you is what matches the xml field name to the database field name.
On the left is the xml field name and on the right the db field name.
<Airport>
<Name> -> AirportName
<State> -> Empty, not a field in the db
<City> -> AirportCity
<Icao> -> ICAO
<Iata> -> IATA
<Lattitude> -> Latitude
<Longitude> -> Longitude
<Elevation> -> Elevation
<MagneticVariation> -> MagVar
<Comments> -> AirportComments
<LastUpdate> -> Empty, not a field in the db
<RunwayList>
<MagneticHeading> -> MagHdg
<MagneticHeadingDate> -> Empty, not a field in the db
<Strength> -> Empty, not a field in the db
<MaxLength> -> Empty
<Width> -> RwyWidth
<Shoulder> -> Empty, not a field in the db
<Comments> -> RunwayProcedure
<LastUpdate> -> Empty, not a field in the db
<QfuList>
<Name> -> RunwayName
<Applicability> -> ActypeRunway
<Asda> ->ASDA
<Lda> -> LDA
<Toda> -> TODA
<Tora> -> TORA
<Slope> -> Slope
<EntryAngle> -> Empty, not a field in the db but as to return 0
<ThresholdElevation> -> ThresholdElevation
<TheresholdLatitude> -> Empty, not a field in the db
<TheresholdLongitude> -> Empty, not a field in the db
<ApproachSlope> -> Empty, not a field in the db
<GlideSlope> -> Empty, not a field in the db
<IncrementGaHeight> -> Empty, not a field in the db
<GroovedPfcSurfaceTO> -> TakeoffRunwaySurface (return 0 if normal or return 1 if grooved)
<GroovedPfcStopway> -> TakeoffStopwaySurface (return False if normal or return True if grooved)
<RunwayPavement> -> RunwayPavement (return False if paved or return True if Unpaved)
<GroovedPfcSurfaceLD> -> LandingrunwaySurface (return False if paved or return True if Unpaved)
<Comments> ->RunwayProcedure
<ToComments> -> TakeoffProcedure
<LdComments> -> LandingProcedure
<LastUpdate> -> Empty, not a field in the db
<MinKVs> -> Empty, not a field in the db
<MaxKVs> -> Empty, not a field in the db
<ThrRedHeight> -> THR_RED_HEIGHT
<AccHeight> -> AccelerationHeight
<InitialClbSpeed> -> CLIMBSPEED
<SpeedLimitAlt> -> SPEEDLIMITALT
<FinalCLBSpeed> -> Empty, not a field in the db
<ObtacleList>
<Distance>-> Dist
<Elevation> -> Ht
<LateralDistance> -> LatOffset
<ObstacleNature> -> Specification
<Comments> -> ObstacleComments
<LastUpdate> -> Empty, not a field in the db
<SIDConstraintList>
<TargetAltitude> -> Empty, not a field in the db
<Gradient> -> DepartureGradient

Hope it helps,
Thank you again,
Mat

Mat1994 · Feb 13, 2019

Just added a field in my userinterface form.
I've attached the database and the xml fields.

Mat

sxschech · Feb 13, 2019

Hi Mat,
Downloaded your latest attachments and started in on this. Any chance you could fill in the blanks for me so I am not spending all of my time recreating your data. I am attaching two files, first is test_rs1.xml which is the file you would edit. The edits would be to any line that begins with a single quote or those lines that have hard coded values that need to refer to the fields in your tables.

Hopefully the pattern will make sense, so each place that you need populated from the table you would substitute the actual value with rs!fieldname

Example:
Print #1, <Name>BOSTON(LOGAN).MA</Name>
becomes...
Print #1, "<Name>" & rs!AirportName & "</Name>"

I have also attached a truncated sample output file based on the fields that I have already keyed in. Let me know if this is on the right path. That file is 20190212_1954.xml
The file name is based on our previous discussion of date + time.xml

Probably can return to this on Thursday evening.

Another question...

<State> -> Empty, not a field in the db

How does this get filled in since your sample showed a state while you indicate it isn't available, do we need to worry about this?

Mat1994 · Feb 14, 2019

Hi,

First of all, thank you very much for your help.

I am attaching two files, first is test_rs1.xml which is the file you would edit. The edits would be to any line that begins with a single quote or those lines that have hard coded values that need to refer to the fields in your tables.

Hopefully the pattern will make sense, so each place that you need populated from the table you would substitute the actual value with rs!fieldname

Yes, the pattern did make sense. I've filled in the blanks so each field refers to the right field of the db. I've attached the file. I've also added a question concerning 4 fields, you'll found them in the attached document.

Question :
Concerning the section Print #1, "<ObstacleList>" and Print #1, "<SIDConstraintList>". Would it be possible to make a loop? Because the number of obstacles and of SIDContraints can change from one runway to another.

I have also attached a truncated sample output file based on the fields that I have already keyed in. Let me know if this is on the right path. That file is 20190212_1954.xml
The file name is based on our previous discussion of date + time.xml

It seems ok. The problem I've noticed concerns the obstacles and SIDContraints.
For the obstacle the structure needs to be displayed like : (Do you think we could create a loop the repeat this structure for each obstacle?)

Code:

Print #1, "<Obstacle>"
Print #1, "<Distance>" & rs!Dist &"</Distance>"
Print #1, "<Elevation>" & rs!Ht &"</Elevation>"
Print #1, "<LateralDistance>" & rs!LatOffset &"</LateralDistance>"
Print #1, "<ObstacleNature>" & rs!Specification &"</ObstacleNature>"
Print #1, "<Comments>" & rs!ObstacleComments & "<Comments>"
Print #1, "<LastUpdate />"
Print #1, " </Obstacle>"

For the SIDContraints, the structure needs to be displayed like this : (Do you think we could create a loop for each SIDContraints?)

Code:

Print #1, "<SIDConstraint>"
Print #1, "<TargetAltitude />"
Print #1, "<Gradient>" & rs!DepartureGradient &"</Gradient>"
Print #1, "<Comments>" & rs!Procedure & "</Comments>"
Print #1, "<InformationDate />"
Print #1, "</SIDConstraint>"

Another question...
Quote:
<State> -> Empty, not a field in the db
How does this get filled in since your sample showed a state while you indicate it isn't available, do we need to worry about this?

No need to worrie about this field. I talked to my supervisor and he said that this field is not importante. It's not in the db because it confusing the engineers I work with. It will have to stay blank in the xml.

If you need more information, please let me know,
Mat

sxschech · Feb 17, 2019

Hi Mat,

Still a bit under the weather and taking something that makes me drowsy, so will take longer than I planned to get this fully up and running. Anyway, here is a new sample output. Seems there were a few typos and other issues that I had to debug in the xml code line by line. Haven't implemented the logic for those lines you indicated about "normale" yet. I put in a preliminary loop for the Obstacles. Got an error on CLIMBSPEED. The closest field name I found was CLIMBSPEEDLIMITSTATUS so substituted that, if not the right field, please let me know which to use.

Mat1994 · Feb 19, 2019

Still a bit under the weather and taking something that makes me drowsy

Thank you for your time and effort but don't give yourself to much work for me. If you can't handle it, let me know and I could trie to finish what you started.

Got an error on CLIMBSPEED. The closest field name I found was CLIMBSPEEDLIMITSTATUS so substituted that, if not the right field, please let me know which to use.

Yes, my mistake. The correct field is CLIMBSPEEDLIMITSTATUS.

Concerning your sample.
I've noticed that in the xml the obstacles are not display correctly. In the sample you have given me you have 4 obstacles, whereas there should only be 2.
The obstacles displayed have to be linked to the runwayname. For example :
You have airport A, runway A, then Obsatcle A1,A2. Then you repeat the same operation. You have airport A, runway B, then obstacle B1,B2 and so on.

Lert me know if you need more explaination,
Many thanks,
Mat

sxschech · Feb 20, 2019

Thanks for letting me know about the names and the obstacles. I think I will be able to work on this a bit more and then hand it off to you. If I have time after work otherwise would continue with it on the weekend. If you need sooner, I'll give you the file as is.

sxschech · Feb 24, 2019

Made some progress. Regarding the following, I would need to know the exact field name in order to put in the logic for the True/False 1/0.

Print #1, "<GroovedPfcSurfaceTO>False</GroovedPfcSurfaceTO>" 'How could you make the field return False if the Database has the value "Normale" or True if the DB has the value Grooved?
Print #1, "<GroovedPfcStopway>False</GroovedPfcStopway>" 'How could you make the field return False if the Database has the value "Normale" or True if the DB has the value Grooved?
Print #1, "<RunwayPavement>1</RunwayPavement>" 'How could you make the field return 1 if the Database has the value "Paved" or 0 if the DB has the value Unpaved?
Print #1, "<RunwayPavementCbr />"
Print #1, "<GroovedPfcSurfaceLD>False</GroovedPfcSurfaceLD>" 'How could you make the field return False if the Database has the value "Normale" or True if the DB has the value Grooved?

Can you change ActypeRunway to a normal field, the multi value field is not playing nice with the code.

Attached is another xml for your review. (doesn't have the fields mentioned above). Hopefully I understood how you wanted the loop set up for the obstacles.

Mat1994 · Feb 26, 2019

Hi,

Print #1, "<GroovedPfcSurfaceTO>False</GroovedPfcSurfaceTO>

Is Print #1, "<GroovedPfcSurfaceTO>" & rs!TakeoffRunwaySurface & "</GroovedPfcSurfaceTO>

Print #1, "<GroovedPfcStopway>False</GroovedPfcStopway>"

Is Print #1, "<GroovedPfcStopway>" & rs!TakeoffStopwaySurface & "</GroovedPfcStopway>"

Print #1, "<RunwayPavement>1</RunwayPavement>"

Is Print #1, "<RunwayPavement>" & rs!RunwayPavement & "</RunwayPavement>"

Print #1, "<GroovedPfcSurfaceLD>False</GroovedPfcSurfaceLD>"

Is Print #1, "<GroovedPfcSurfaceLD>" & rs!LandingRunwaySurface & "</GroovedPfcSurfaceLD>

Can you change ActypeRunway to a normal field, the multi value field is not playing nice with the code.

Ah! The ActypeRunway... So I've talk to my supervisor about this field.
What i've learnt is that the field doesn't need to be exported so you can leave it like this :

Code:

<Applicability> </Applicability>

But the idea behind this field is, and I'm wondering if it's possible to do so :
We would like to use ActypeRunway as a filter. When the user clicks on the "export to Airbus", there is a pop up page with a liste of all the aircraft (based on the liste in the table (tbl_Aircraft)). There, the user can chose which aircraft he want and the export will be what you are working on.
I don't know if it's clear enough. Let me know if you want more explanation.
But do you thing it's possible to do so, or do you have any suggestion?

Attached is another xml for your review. (doesn't have the fields mentioned above). Hopefully I understood how you wanted the loop set up for the obstacles.

Gone throught your xml. It seems to be ok, I didn't see anything wrong with it. Thank you a lot for your help.

Mat

Exporting in xml using a module (1 Viewer)

Registered User.

I’m here to help

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Similar threads

Users who are viewing this thread