Generate/form XML output from Access DB

[mmattson]

I am good with DB's and HTML - only played a little with XML. Don't know some of the correct terminology so please correct any misuse of terms.

I am being asked to export data from databases (Access & SQL) to XML files for online data submission. I am initially working with an Access DB. I have created a query that returns the data I need to submit online via a web services (wsdl) URL.

My challenge is to generate an XML file with the appropriate header, entities, namespaces, etc.

The following are excerpts of the first node(s), not complete file.

My current XML export from Access looks something like this:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="XMLtest.xsd" generated="2009-03-25T21:50:33">
<XMLtest>
<UserID>username</UserID>
<Password>password</Password>
<SubmissionCtrlNum>2009326</SubmissionCtrlNum>
<CUSIP9>123456789</CUSIP9>
<InstrumentType>V</InstrumentType>
<TransactionType>I</TransactionType>
<DealerMSRBNum>12345</DealerMSRBNum>
<Date>2009-03-26T00:00:00</Date>
<Time>16:30:00.00</Time>
<InterestRatePeriod>7</InterestRatePeriod>
<NotificationPeriod>7</NotificationPeriod>
<InterestRate>2.53999996185303</InterestRate>
<RateType>R</RateType>
<ParAmountRemarketed></ParAmountRemarketed>
<MinDenomination>100000</MinDenomination>
</XMLtest>

What I need is something like this:

<?xml version="1.0" ?>
<SubmitterInput xmlns="http://www.msrb.org/avts/submitter"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:avts="http://www.msrb.org/avts/common"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xsi:schemaLocation="http://www.msrb.org/avts/submitter SubmitterInput.xsd
http://www.msrb.org/avts/common Common.xsd">
<Submitter>
<avts:UserID>bthompso1234567</avts:UserID>
<avts:SubmitterMessageTimeStamp>
<avts:Date>2008-08-22</avts:Date>
<avts:Time>15:00:00</avts:Time>
</avts:SubmitterMessageTimeStamp>
<avts:SubmissionCtrlNum>2008082200000001</avts:SubmissionCtrlNum>
<avts:InformationType>ResetRate/Liquidity</avts:InformationType>
<avts:Password>password0123456</avts:Password>
</Submitter>
<Transactions>
<Transaction>
<TransactionType>I</TransactionType>
<Instrument>
<avts:CUSIP9>123456AB1</avts:CUSIP9>
<avts:InstrumentType>V</avts:InstrumentType>
</Instrument>
<Dealers>
<avts:DealerMSRBNum>A1234</avts:DealerMSRBNum>
</Dealers>
<RateInformation>
<InterestRateResetDateTime>
<avts:Date>2008-09-22</avts:Date>
<avts:Time>12:00:00</avts:Time>
</InterestRateResetDateTime>
<InterestRatePeriod>7</InterestRatePeriod>
<NotificationPeriod>7</NotificationPeriod>
<InterestRate>4.250</InterestRate>
<MinDenomination>100000</MinDenomination>
<RateType>F</RateType>
<ParAmountRemarketed>45000000</ParAmountRemarketed>

[Charles]

Don't bother exporting Access to XML and then monkeying with the XML. It's better to get the XML you want straight from Access. Where are you trying to do this? On you desktop? On the server? What's the OS and what scripting do you have available?

[mmattson]

Windows XP and/or Vista. Access 2003 and/or 2007. I have VS 2005 - take your choice. The Access DB is used by 1 individual on their desktop (Access 2003/WinXP), but the MDB lives on a public share drive.

I use both XP and Vista, and Access 2003 and 2007 so can play in either place.

[Charles]

See http://www.webdeveloper.com/forum/showthread.php?t=202501 and start reading around post #23 and you'll want to see and book mark http://www.connectionstrings.com/ . If you need more detail then see http://www.microsoft.com/technet/scriptcenter/resources/officetips/nov05/tips1103.mspx .

[mmattson]

Thanks! I'll do my homework from the links and post back my results.

[mmattson]

If I am using a parameter (date requested), what is the best way to pass that value to the script?

[Charles]

Put the script inside a Windows Script Host file (It takes a ".wsf" filename extension. That one always trips me up.) That'll give you access to the command line parameters and some other useful things. See http://msdn.microsoft.com/en-us/library/98591fh7(VS.85).aspx for details. And yes, I do understand that you are falling deeper down the rabbit hole.

[mmattson]

It's all good. I like these rabbit holes. Hopefully I can return the favor to someone else someday.

The most recent issue is:

While testing without a parameter, I get a run time error on line 27,char 17 (Type Mismatch).This is the line creating text node.

while (!rs.eof) {
record = dom.createElement ('record');
for (i = 0; i < rs.fields.count; i++) {
tagName = String (rs.fields(i).name).replace (/\W/g, '');
node = dom.createElement (tagName);
node.appendChild (dom.createTextNode (rs.fields(i).value));
record.appendChild (node);
}
root.appendChild (record);
rs.moveNext();

I will be creating a mix of number, text, and date/time fields. How to handle? Or is this indicating another issue?

[Charles]

It could be a null value. If you are more comfortable with SQL then have some fun with NVL or COALESCE and make sure that every field returns a string value. I usually end up using things like:node.appendChild (dom.createTextNode (rs.fields(i).value || ''));And if you are using parameters then you might find one or more of these two hints helpful. You're sending the query as a string so your script can substitute your parameter for anything in the query. And you can apply something sort of like SQL on the return from the query. See http://www.w3schools.com/ado/ado_ref_recordset.asp and especially check out the "filter" property.

[mmattson]

I've been on W3schools' site since last night...I'm learning as quickly as I can. I'm glad to see I'm barking up the right tree.

It was the NULLS that threw the error. I seem to have that resolved.

As far as formatting to my assigned requirements (first post)...I guess it's easy enough just to make the header lines static. Any recommendations on how to automate adding the namespace info to the nodes/tags that I referenced in the first post?

If there are no tools/scripting that make it a one-step process, I've got a tool that can be a second-step find/replace to create the final file.

Thanks for your help!

[Charles]

Namespaces, I forgot about that. Microsoft being Microsoft they make useful things but everything is just a little bit off. I'm just now reminded that when working with namespaces you have to register the namespace and then use another metod to create the elements. Spend some time with Google. That's how I solved that problem last time.