Thursday, May 31, 2012 12:09 PM
This is a new area for me. I’ve had a look at the built-in XML support in Access and can’t figure out how to do what I want with it.
This is the challenge:
I have an Access database with various tables from which I currently export a flat text file specifying amounts to be collected by Direct Debit (direct collection from a customer bank account). The bank that processes this file now wants the same data in a specific XML format as defined in a 28 page specification document (i.e. a rather more complex file).
I see that I can export my table data as an XML file as long as I create relationships within Access defining how the various tables are related. My problem is that I can only create these relationships by using Primary and Foreign keys in the tables whereas there are no such fields in the Bank-supplied data structure.
I’ve imported an example bank-supplied XML file and I duly get the 18 tables Access finds in the data. But I don’t get any relationships or fields against which to define them.
My solution is to go back to ground I’m more familiar with and write my data out to a text file that is in the valid XML format using Print. I think that will work but it seems a very old fashioned way of doing it. If anyone knows how I could use built-in XML support instead I’d love to hear about it.
Thanks for any ideas
Thursday, May 31, 2012 1:31 PM
The bank will (should) have supplied an XSD file, which describes the format of the XML file. The 28-page document is just fluff for humans.
When you create a query that pulls the data (or just some of the data for starters), from the ribbon (I'm in A2010) choose External Data > Export > XML File, and in the second step of the wizard click on More Options > Schema Tab, and you can specify the XSD.
If the bank did not supply an XSD, harras them and don't start this project until they do. It's easy for them, near-impossible for you.
-Tom. Microsoft Access MVP
Thursday, May 31, 2012 2:53 PM
Thanks for that.
I don't have an XSD file to go with their XML file and will ask them for that now.
I still have a question about how I'll use that XSD file though.
When I look at the A2010 ribbon as you indicate, it looks like in the Schema tab you can specify where to create a separate XSD file to go with your XML export but it doesn't look like you can select an existing file for the export to automatically conform to. How should be understanding this?
Monday, June 04, 2012 8:35 PM
I think that Tom has provided some very good information regarding the XSD/XML files. The document from the bank will provide the schema of the database and the XML file.
The XSD will provide you with the schema of their database. The schema information may also be embedded in the bank's sample XML file. The key of this project will be to get the schema of the banks database so you can mimic the relationships of the bank's database.
Then the data that you export that specifies the amounts to be collected by Direct Debit should be able to be imported into the bank's database.
However, if your database and the bank's database schemas are vastly different, then the flat text file route may be your best option.
Please see the following for additional information:
New XML Features in Microsoft Office Access 2003
If I am misunderstanding your issue, please clarify the situation to better assist. Is there something that is not working correctly on either the bank's side or your side?
Dan F - Microsoft Online Community Support
Wednesday, June 13, 2012 8:47 AMDan
Thanks for your response.
I’ve been on holiday for a week and so did not respond sooner.
I think I may have confused the issue by talking about relationships in the data.
I’ll try and put the crux of my question differently.
The XML file the bank require has many envelope nodes of which some are nested within each other. Many of these are mandatory even where is no data within the envelopes. This snippet of a single payment record from an example file shows what I mean;
<AdrLine>278 avenue Roland Garros</AdrLine>
Assuming I get all of the data into a single table / query first I can see how I could then export that from Access as a single XML export with the field names coming out as tags either side of each piece of data and with the Table / Query name showing as an envelope node around each record.
But I don’t see how I can tell Access to create all the additional envelope nodes needed in the Bank file. I looked at achieving this nesting through relationships between the tables in access, hence my earlier question, but I don’t see how it can be done. I also don’t see how I could get the preceding and subsequent sections of the bank file (entirely different and not shown in my snippet) to appear correctly using a single Access export.
If there is some clever ‘built-in’ way around this I’d love to know about it but otherwise in this case the XML format might just as well be any fixed file format dictated by a large system and I am stuck with my current approach to making the file, i.e. writing out my data line by line using Print.
I’m new to XML so any thoughts on this would be very welcome.
Thanks a lot
- Marked As Answer by Ed_Secretan Wednesday, June 13, 2012 10:54 AM
Wednesday, June 13, 2012 10:21 AM
If you have to create really complicated Xml documents there is no other way but using DOM object
Its a lot of dearty work a lot of coding...
I'm adding one of my functions for you to study. Hope it will help.
Public Function XMLcreateProcessingInstruction(Serviceneeded As String, LicenseNo As String, Optional Batch As Boolean) Dim strFilePath As String Dim rs As Recordset2 Dim RootName As String Dim MsgName As String Dim PniaCode As Long Dim objNode As MSXML2.IXMLDOMNode Dim objNodeLevel2 As MSXML2.IXMLDOMNode Dim objElemLevel2 As MSXML2.IXMLDOMElement Dim objElem As MSXML2.IXMLDOMElement On Error GoTo Err_XMLcreateProcessingInstruction Set rs = Db.OpenRecordset("select * from A_Services where ServiceName = """ & Serviceneeded & """") If rs.RecordCount > 0 Then PniaCode = rs!PryID If Batch Then RootName = "Envelope" MsgName = rs!ServiceType Else RootName = rs!ServiceMethod MsgName = rs!ServiceType End If End If rs.Close Set rs = Nothing strFilePath = "C:\client\TestXML.xml" Set objNode = objDocXml.createProcessingInstruction( _ "xml", "version='1.0' encoding='UTF-8'") Set objNode = objDocXml.InsertBefore(objNode, _ objDocXml.ChildNodes.Item(0)) Set objRoot = objDocXml.createElement(RootName) Set objDocXml.DocumentElement = objRoot If Batch Then objRoot.setAttribute "xmlns:ns0", "od" & LicenseNo & "_b_tucna" Else objRoot.setAttribute "xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance" End If If Batch Then Set objElem = objDocXml.createElement("Header") objElem.setAttribute "Type", MsgName objRoot.appendChild objElem Set objNode = objDocXml.createElement("Source") objNode.text = "od" & LicenseNo objElem.appendChild objNode Set objNode = objDocXml.createElement("Interface") objNode.text = "b_tucna" objElem.appendChild objNode Set objNode = objDocXml.createElement("Target") objNode.text = "haba_bama" objElem.appendChild objNode Set objNode = objDocXml.createElement("Year") objNode.text = Format(Now, "yyyy") objElem.appendChild objNode Set objNode = objDocXml.createElement("SerialNo") If Codbc Then objNode.text = Nz(f_BringLastRecord("TPA027Numerator", "RequestSubj=" & PniaCode & " and ReqYear <> '0000'", "SerialNo"), 0) + 1 Else objNode.text = Nz(DLookup("max(SerialNo)", "TPA027Numerator", "RequestSubj=" & PniaCode & " and ReqYear <> '0000'"), 0) + 1 End If objElem.appendChild objNode Set objNode = objDocXml.createElement("CreationTimeDate") objNode.text = Format(Now, "yyyy-mm-ddThh:mm:ss") objElem.appendChild objNode Set objNode = objDocXml.createElement("NoOfRecords") objNode.text = 1 objElem.appendChild objNode Set objElem = objDocXml.createElement("Body") objRoot.appendChild objElem Set objElemLevel2 = objDocXml.createElement("Message") objElemLevel2.setAttribute "Type", MsgName objElem.appendChild objElemLevel2 Else Set objElem = objDocXml.createElement("RequestHeader") objRoot.appendChild objElem Set objNodeLevel2 = objDocXml.createElement("RequestId") If Codbc Then objNodeLevel2.text = Nz(f_BringLastRecord("TPA027Numerator", "RequestSubj=" & PniaCode & " and ReqYear = '0000'", "SerialNo"), 0) + 1 Else objNodeLevel2.text = Nz(DLookup("max(SerialNo)", "TPA027Numerator", "RequestSubj=" & PniaCode & " and ReqYear = '0000'"), 0) + 1 End If objElem.appendChild objNodeLevel2 Set objNodeLevel2 = objDocXml.createElement("UserType") objNodeLevel2.text = "úæ" objElem.appendChild objNodeLevel2 Set objNodeLevel2 = objDocXml.createElement("UserId") If Codbc Then objNodeLevel2.text = Nz(f_BringLastRecord("THA001", "Account_Hesbon='" & LicenseNo & "' ", "ID_No"), 0) objNodeLevel2.text = objNodeLevel2.text & Nz(f_BringLastRecord("THA001_Av", "Account_Hesbon='" & LicenseNo & "' ", "Bikoret_No"), 0) Else objNodeLevel2.text = Nz(DLookup("ID_No", "THA001Tmp", "Account_Hesbon=""" & LicenseNo & """"), 0) objNodeLevel2.text = objNode.text & Nz(DLookup("Bikoret_No", "THA001Tmp", "Account_Hesbon=""" & LicenseNo & """"), 0) End If objElem.appendChild objNodeLevel2 Set objElem = objDocXml.createElement("Message") objElem.setAttribute "Type", MsgName objRoot.appendChild objElem End If Call FormatDocToFile(objDocXml, strFilePath) Exit_XMLcreateProcessingInstruction: Exit Function Err_XMLcreateProcessingInstruction: MsgBox Err.Description & " envelop data " Resume Exit_XMLcreateProcessingInstruction Resume End Function Private Sub FormatDocToFile(ByVal doc As MSXML2.DOMDocument, _ ByVal filename As String) 'Reformats the DOMDocument "Doc" into an ADODB.Stream 'and writes it to the specified file. ' 'Note the UTF-8 output never gets a BOM. If we want one we 'have to write it here explicitly after opening the Stream. Dim rdrDom As MSXML2.SAXXMLReader Dim stmFormatted As ADODB.Stream Dim wtrFormatted As MSXML2.MXXMLWriter On Error GoTo FormatDocToFile_err Set stmFormatted = New ADODB.Stream With stmFormatted .Open .type = adTypeBinary Set wtrFormatted = New MSXML2.MXXMLWriter With wtrFormatted .omitXMLDeclaration = False .standalone = True .byteOrderMark = False 'If not set (even to False) then '.encoding is ignored. .Encoding = "utf-8" 'Even if .byteOrderMark = True 'UTF-8 never gets a BOM. .Indent = True .Output = stmFormatted Set rdrDom = New MSXML2.SAXXMLReader With rdrDom Set .contentHandler = wtrFormatted Set .dtdHandler = wtrFormatted Set .errorHandler = wtrFormatted .putProperty "http://xml.org/sax/properties/lexical-handler", _ wtrFormatted .putProperty "http://xml.org/sax/properties/declaration-handler", _ wtrFormatted .Parse doc End With End With .SaveToFile filename, adSaveCreateOverWrite .Close End With FormatDocToFile_exit: Exit Sub FormatDocToFile_err: MsgBox Error$ Resume FormatDocToFile_exit Resume End Sub
Wednesday, June 13, 2012 10:54 AM
Thanks for that.
Your code is really helpful as I see it gives me the gist of what would be needed. I had a feeling there was no magic shortcut on this one but equally I knew the way I was going about it was old fashioned at best. There's a lot of stuff in your code which is new to me and will be worthwhile getting to grips with. On this particular project though I'm now more than 1/2 way through getting it sorted my way and have just been chased again on the delivery so I'll plough on with what I have rather than embark on a lot of new learning. I'd like to come back to your code though when I'm in "learning" mode rather than "deadline" mode.