Export to XML

[Les Isaacs]

Hi All

I need to export data from our access 2010 accdb to various xml files that adhere to very specific schemas (for submission to the Inland Revenue). I'm pretty sure this can't be handled by access's built-in 'export to xml' facility, and needs to be done with vba instead - but although I'm pretty good now with writing vba functions, etc., I don't know where to start with this
I may well be that I need to pay someone to do this, or to train me to do this. I'm based on the Wirral in the UK - although it could probably be done remotely (using skype, or logmein?).

Hope someone out there can help.

Thanks
Les

 

[speakers_86]

You can set a reference to microsoft xml and do it with a routine or two. My recent post in the samples forum shows you how to do it. Its the ribbon builder post. Look in the module behind frmRibbons.

 

[Les Isaacs]

Hello 'speakers_86'

Many thanks for your reply. I have downloaded and had a look at your Ribbon Builder.mdb, and I think this could give me the ability to do what I need to do ... but I'm a bit out of my depth! I am very comfortable with coding in vba in general, but would find it extremely helpful if you could have a look at the (rather long!) schema specification document for the xml files that I need to be able to generate, (there are also some examples of the xml files), so that you could advise whether or not your Ribbon Builder.mdb would be a feasible solution ... and if so if you could help me get to grips with using your Ribbon Builder.mdb.

Obviously I would be prepared to pay for your time. Is this an option?

Thanks again
Les

 

[speakers_86]

Im a soldier, not a programmer, and this is a free forum. just post an example of what your xml needs to look like, and how your tables are laid out, and ill see what i can do.

 

[Les Isaacs]

Hi 'speakers_86'

Ok - I appreciate your offering to help for free

I have tried to upload the sample xml files with this post, but it seems xml files are not permitted as uploads!

The sample xml files are available from the HMRC website, at
http://www.hmrc.gov.uk/softwaredevelopers/rti/internet-rti.htm
(under the heading 'Technical Packs', then the subheading 'Valid Samples')

If you need also to see the schema documentation, it's attached to this post: it's quite long and detailed though

Many thanks for your continued help.

Les

 

[speakers_86]

That schema looks far more comprehensive then anything microsoft has put out for the ribbon!

I can't tell you how to export your tables without seeing your tables. Post it here, but use fake data.

 

[Les Isaacs]

Hi 'speakers_86'

You're right about that schema document - so much detail - but I'm sure that if I can get to the point of being able to generate, from our data, an xml file that 'matches' the sample xml FPS file, it will be relatively simple to add further fields/elements, and validation, etc., by reference to the schema document.

Does that sound right to you?

I have attached an accdb (zipped) that includes the main tables that hold the required data. Obviously I will need to map the table fields to the xml elements, and write queries to use for the generation of the xml files - but I can do that relatively easily (if not quickly!). The bit I'm stuck on is actually creating an xml file that is in the required schema, with our access data in it!

Hope this provides the info you need, so that you can help with the bit I'm stuck on

Thanks once again
Les

 

[speakers_86]

Before you get into trying to export this, you need to make sure your storing it correctly. You've got spaces and % in your field names, you don't want any special characters. Just alpha numeric and underscores. That also goes for your object names.

Also, this isn't quite normalized. You should look into normalization. One place I saw this is that a practice can have multiple banks, but that bank account info is stored in the practice table. It should have it's own table.

Once you fix that stuff, then you can try and export it

The procedure would be to set a reference to MSXML 6.0. Start a new sub, dimension a new DomDocument, and use the CreateNode method to start adding nodes. To add all employees, you would set a recordset to a query of your employees, and loop through that recordset, creating the nodes. In the attached, I show you how to add all your employees. There is a lot missing, but I show you how to add nodes, how to loop through recordsets, and that is the hardest part about that.

From the immediate window, type XML and hit enter. It will get a message box showing you the content of the dom document. It will show and EmployeeDetails node for each of your 114 employees. Try and have a go at the rest. The skeleton is there!

 

[gemma-the-husky]

you can probably write an xml file directly

it's this sort of logic

write opening tag
write inner detail
write closing tag

now - if the inner detail includes further embedded structures, or there are multiple instances of the outer tag pair - it can become a lot easier to do if you manage this with a recursive algorithm of some sort.

if there are no embedded structures, it's pretty straightforward.

 

[speakers_86]

Of course you can do it that way. I prefer using the MSXML reference though. Plus, if you can figure out how XPath works, you've got a pretty powerful tool. I haven't quit got it yet though.

 

[Les Isaacs]

Hi 'speakers_86'

Many thanks for your guidance on this - it's very much appreciated.

I certainly accept that some of our field names are not ideal, but my excuse is that I inherited this accdb and it's extremely complex and I am getting round to things like fieldnames, etc. Believe it or not, the 'staff_name' field used to be 'name':banghead:

The lack of full normalisation is generally intended: although I know Ted Codd wouldn't have approved there are performance benefits to be gained . With that said, I'm not sure what you mean about Practices having more than 1 bank account - they don't. (They do have a Practice bank account and an HMRC bank account, but each of these will always be different from each other and from those of other Practices.)

I've had a look at the module in the accdb you provided, and get the gist of it, but (and I know this will be a stupid question) where it has
strXMLns = "YourNameSpace"
should I substitute "YourNameSpace" with the path and file name for the exported xml file (so e.g. "C:\payedoc programs\testXMLfile.xml")? I tried this, but nothing apeared in C:\payedoc programs

Second question: before I really get into using this method, do you think it will be reasonable and feasible to use it to produce the complex xml files that I need? Not sure if you've seen any of the examples - I wasn't able to attached any to this thread as xml files aren't allowed - but they are at

http://www.hmrc.gov.uk/softwaredevel...ternet-rti.htm
(under the heading 'Technical Packs', then the subheading 'Valid Samples').

Third (and final) question: how does this approach relate to your ribbon builder post, and frmRibbons?

Thanks again for all the help.
Les

 

[Les Isaacs]

Hi 'gemma-the-husky'

Many thanks for coming in on this.

I'm obviously a bit out of my depth here, but why might it be preferable to write the xml file directly with code, rather than using the method suggested by 'speakers_86'? Would it be because it would be more flexible, or quicker?

To answer this it may be necessary to see one of the examples of xml files that I need to generate - these are at

http://www.hmrc.gov.uk/softwaredevel...ternet-rti.htm
(under the heading 'Technical Packs', then the subheading 'Valid Samples').

If you do think the direct writing would be a good idea, would you be able to give me a steer about how to actually do it . I understand the general logic that you suggested, but have no idea how to do it

I will say here that I am prepared (keen!) to pay someone to really get this going. Obviously I will need to 'map' our data fields to the corresponding xml file elements (or is it nodes), but it would be great if I could find someone to take the rest of the job on

Thanks again for any help.
Les

 

[speakers_86]

You've got the wrong idea about the namespace. When you are working with two different schemas, the namespace allows you to differentiate between conflicting node elements. If you xml were talking about database objects and furniture, then what is a table? If that doesn't make sense, google can explain it better. If you want to save the xml file, you can use the Dom document's save method (oDom.save "your path here").

What Dave is suggesting is that you dimension a string and fill the string recursively. It is the same thing I'm telling you to do, except his method does not involve the reference to MSXML.

Public Sub CreateXML
   dim str as String
   str="<node "
   ' fill the string node here
   str= " />"
End Sub

The reason I prefer MSXML is that I feel like it can handle the closing tags better than I could, and I feel like the reference can handle changes to the data better. But maybe that's just me. Plus you've got XPath and the save method, both pretty useful.

Second question: before I really get into using this method, do you think it will be reasonable and feasible to use it to produce the complex xml files that I need? Not sure if you've seen any of the examples

Yes, I saw the examples. That is actually what I based the sample that I uploaded on. The sample called AES sample from April. I see no reason why you can't use this method.

Third (and final) question: how does this approach relate to your ribbon builder post, and frmRibbons?

The sample shows you how to manipulate a Dom Document. If you look at the function CreateXML and the recursive sub that it calls, you will see that, its just like in the sample I posted here.

, but it would be great if I could find someone to take the rest of the job on

This forum is mostly developers helping developers. There is another sub forum here where you can hire some one for such tasks. It's not a very active forum, so I'm not sure if you will find any one. It would probably be better off doing it yourself. Who understands your data better than you? You've got a ton of fields in those tables of yours. Besides, I've told you how to do it! Admittedly, this is no place for a beginner to start.

 

[gemma-the-husky]

just to clarify.

you are probably trying to transform data held in a query, or on a number of variables into a xml file.

now i think you could use some sort of xslt file to produce the final file. on the other hand, if you don't have proficiency with using this method, which i do not, then it is quite easy to just output the xml file.

and effectively you get this sort of thing.

write outer opening tag
for each field in query.fields
write opening tag
write data
write closing tag
next
write outer closing tag


it's similar to generating csv files. sometimes it's easier to write the things manually, as transfertext may not be appropriate for the file.

 

[Les Isaacs]

Hi 'speakers_86'

I'm still not getting this

There's obviously quite a lot to it, and I may well have to persevere, but

There is another sub forum here where you can hire some one for such tasks

... where is the sub forum?

Thanks as ever
Les

 

[spikepl]

I can give you an outrageous offer - PM me. It has the advantage that any other offers you subsequently run into will seem very reasonable

 

[spikepl]

If you did receive my PM then please confirm, otherwise say you didn't