Generate an XML file from Database

[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.

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="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL]  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="[URL unfurl="true"]http://www.msrb.org/avts/submitter"[/URL]
xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL]
xmlns:avts="[URL unfurl="true"]http://www.msrb.org/avts/common"[/URL]
xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL]
xsi:schemaLocation="[URL unfurl="true"]http://www.msrb.org/avts/submitter[/URL] SubmitterInput.xsd
[URL unfurl="true"]http://www.msrb.org/avts/common[/URL] 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>

 

[Diancecht]

Well, looks like an XSLT transformation could be the way to go but what kind of language are you planning to use?

Cheers,
Dian

 

[mmattson]

That's why I am asking... I have VS 2005. Which language do you recommend.

 

[Diancecht]

You can do it in any language, that will depends on your needs (portabiliy, platform requirements). I wanted to know to look for a suitable example.

With VS2005 I understand .net?

Cheers,
Dian

 

[mmattson]

YEs .net is fine. Thanks.

I have learned more about the requirements. I need to build an application that will connect to their web services site and upload our transaction data.

 

[Diancecht]

Maybe you should consider writing an app that will retrieve the data and call the webservice without the intermediate XML

This article may help.

Also this one.

Cheers,
Dian

 

[mmattson]

That's where my knowledge breaks down. I have written some basic VB and C apps. I've not called a web service before and don't know where to start.

Not worried about doing the work and learning, just don't know the best place to start.

To refresh...I have transaction data in Access that needs to be sent to another company via WSDL web service site. I need to map my fields to there fields in the format they expect. No big deal, just don't know where to start.

 

[Diancecht]

Didn't the links help?

If not, a google search on webservices tutorial will show you more ways and sample code.

Cheers,
Dian