access and its xml formatting

[davebm]

hi all, first time poster, with an awkward question.

i'll save you my life story but suffice to say i'm just 3 weeks into a 6mnt work placement from college and i could do with a bit of help.

the company i'm working with has to generate an xml document (preferably with access) corresponding to this schema

the problem so far as i can make out is that when i import this xsd into ms access - i'm presented with a rediculous number of tables and in turn these are not related, i.e. there is nothing to say that the e-mail address table is associated with the header table. (having them in one table makes more sense to me, but i'm not an expert, all i know is i have to comply with this xsd.)

when the data is exported to xml format, it's meant to look like this:

<?xml version="1.0" encoding="UTF-8"?>
<EUSavings formversion="1" periodstart="01/07/2005" periodend="31/12/2005" language="E">
<HeaderDetails>
<PayingAgent>
<TaxNumber taxtype="4">1234567T</TaxNumber>
<PayingAgentName>
<NameDetails>Joe Bloggs</NameDetails>
</PayingAgentName>
<PayingAgentAddress>
<AddressLineDetails Type="Line1">Test Road</AddressLineDetails>
<AddressLineDetails Type="Line2">Test City</AddressLineDetails>
</PayingAgentAddress>
<PayingAgentCountryCode>
<CountryCode>IE</CountryCode>
</PayingAgentCountryCode>
</PayingAgent>
<FileSequenceNumber>1</FileSequenceNumber>
<PaymentYear>2006</PaymentYear>
<ContactDetails>
<ContactName>
<NameDetails>Paul O'Neill</NameDetails>
</ContactName>
<TeleNumber>
<TeleNumberDetails>0875252252</TeleNumberDetails>
</TeleNumber>
<EmailAddress>
<EmailAddressDetails>test@test.com</EmailAddressDetails>
</EmailAddress>
</ContactDetails>
</HeaderDetails>
<AccountDetails>
<DocumentType>1</DocumentType>
<AccountHolderDetails>
<FormType>A</FormType>
<KeyName>
<NameDetails>Jones</NameDetails>
</KeyName>
<OtherNames>
<NameDetails>Tom Paul</NameDetails>
</OtherNames>
<Address>
<AddressLineDetails>New Road</AddressLineDetails>
<AddressLineDetails>New City</AddressLineDetails>
</Address>
<AddressCountryCode>
<CountryCode>GB</CountryCode>
</AddressCountryCode>
<BeneficialOwnerResidenceCountryCode>
<CountryCode>FR</CountryCode>
</BeneficialOwnerResidenceCountryCode>
<BeneficialOwnerBirthDetails>
<DateOfBirth>01/01/1945</DateOfBirth>
<BirthCity>Paris</BirthCity>
<BirthCountryCode>
<CountryCode>FR</CountryCode>
</BirthCountryCode>
</BeneficialOwnerBirthDetails>
<PaymentType>0001</PaymentType>
<CurrencyCode>EUR</CurrencyCode>
<AmountPaid>8889</AmountPaid>
<AccountIdentifier>152525525</AccountIdentifier>
</AccountHolderDetails>
<ReferenceNumber>455200211</ReferenceNumber>
</AccountDetails>
</EUSavings>

everyone in this company is completely new to xml and seeing as i have a little experience with it from college i've got quite a bit of pressure on me to try and make this work.

so, my question is: how would i go about exporting data from access into a xml document which conforms to the xtd, and looks like the xml code above?

is access capable of doing this?

they want this solved programatically, but nobody here knows how to do it.

can anyone offer guidelines on how to tackle this? any help would be really appreciated, i'm in over my head

 

[jjh]

Don't know if this will help, but..

The import mechanism in access is kinda dumb (as you found out). Each major tag becomes a table (object) and no relationships are made among the tables. I don't know exactly how to do this but I have a suggestion. You may be able to parse each line in the file and create correct tables in access using VB scripts/functions. That will at least get the right number and names of tables and fields in the tables.

Now you will need to allocate a field for the keys. That is doable in VB. What I cant figure out how to do is embed the key links. What you could try to do is extract the nesting info by parsing, and generate your key information based on the nesting.

A colleague of mine did something very similar to this in JAVA (in Eclipse) but that is probably a lot of overhead for you and, and I could not even begin to explain (or access) his code. Although, he said it was pretty easy (yea, big help to you...not)
Good luck
John
BTW, did you solve this yet? How?

 

[jjh]

I forgot to mention...

If you do not want to go the program you own route, search the web for applets (some even free) that does what you want. If your in a real company, if is often cheaper for them to pay $200-$500 or more for by a program that is embeddable in Access that will do what you want, rather than pay you to do it. Sometimes one just has other 'fish to fry'...here is a pointer to one such set of applets:
http://rustemsoft.com/xmlconverter3.htm#

I don't own or have any interest in this company..hell, I don't know if they have somethig to solve your problem, but they are worth a look, and a serious google search.
Good luck
John

 

[monyi]

Hi davebm, try this.

Public Function FormatXML(ByVal strXML As String) As String
Dim rdr As Object 'MSXML2.SAXXMLReader40
Dim cnth As Object 'MSXML2.IVBSAXContentHandler
Dim wrt As Object 'MSXML2.MXXMLWriter40

Set rdr = CreateObject("MSXML2.SAXXMLReader")
Set wrt = CreateObject("MSXML2.MXXMLWriter")
wrt.indent = True
wrt.omitXMLDeclaration = True
Set cnth = wrt
Set rdr.contentHandler = cnth
rdr.parse strXML

FormatXML = wrt.output
End Function


Private Sub Test()
Dim doc As Object 'MSXML2.DOMDocument
Dim ndArchivos As Object 'MSXML2.IXMLDOMNode
Dim ndArchivo As Object 'MSXML2.IXMLDOMNode

Set doc = CreateObject("MSXML2.DOMDocument")
Set ndArchivos = doc.appendChild(doc.createElement("ARCHIVOS"))
Set ndArchivo = ndArchivos.appendChild(doc.createElement("ARCHIVO"))
ndArchivo.nodeTypedValue = CurrentDb.Name

doc.loadXML FormatXML(doc.xml)
doc.Save CurrentProject.Path & "\Prueba.xml"
End Sub