Adding fields to query to support XML file output

[brianjohnson]

Not sure where this belongs, apologies if it's the wrong place!

I've been asked to produce an XML output from an Access 2003 database for input into another application. The output file format is fixed and includes field names that must exist within the output file. I can provide the key data required using aliases in an Access SQL query ... but there are some fields that don't exist in the database that must be included in the output file even though they don't hold any values. How can I add these (empty) fields other than by manually editing all the records? Is it possible to do this within a query? I guess I could create a table that includes all the required fields and then update this table from a query ... seems a lot of work ...!

How would you approach this if you were the owner of such a database?

Thanks all, Brian

 

[jdraw]

Something like this might apply

SELECT fld1,fld2, fld3,"" as MissingfldA,"" as MissingfldB from yourTable.

More details and/or samples and layout would help.

 

[brianjohnson]

jdraw, that looks spot on. A test has generated the right info in the XML file. Thanks so much. Off now to load up and test the rest!

Best regards, Brian

 

[brianjohnson]

With jdraw's help, I can get the output fields that I need ... but need to understand more about sorting and grouping. In the attachment, I can now get fields such as Starts, start, entries and entry by using an alias (i.e. "" as Starts) but the output file needs to be sorted/grouped by those 'empty' fields as I have tried to show in the attachment. I could sent the original complete file and the fields that I don't have in the database if that would help get the *right* query output ... it's several pages long

with thanks, Brian