none
Exporting data to an externally defined XML Schema RRS feed

  • Question

  • 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

    Regards

    Ed

    Thursday, May 31, 2012 12:09 PM

Answers

  • Dan

    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;
    - <DrctDbtTxInf>
    - <PmtId>
      <EndToEndId>48538.49665</EndToEndId> 
      </PmtId>
      <InstdAmt Ccy="EUR">229.51</InstdAmt> 
    - <DrctDbtTx>
    - <MndtRltdInf>
      <MndtId>0005363</MndtId> 
      <DtOfSgntr>2011-03-01</DtOfSgntr> 
      <AmdmntInd>false</AmdmntInd> 
      </MndtRltdInf>
    - <CdtrSchmeId>
    - <Id>
    - <PrvtId>
    - <OthrId>
      <Id>GB26ZZZSDDNWBK94894701</Id> 
      <IdTp>SEPA</IdTp> 
      </OthrId>
      </PrvtId>
      </Id>
      </CdtrSchmeId>
      </DrctDbtTx>
    - <DbtrAgt>
    - <FinInstnId>
      <BIC>CCFRFRPP</BIC> 
      </FinInstnId>
      </DbtrAgt>
    - <Dbtr>
      <Nm>AVANTAGES SERVICES</Nm> 
    - <PstlAdr>
      <AdrLine>278 avenue Roland Garros</AdrLine> 
      <AdrLine>BP 413</AdrLine> 
      <Ctry>FR</Ctry> 
      </PstlAdr>
      </Dbtr>
    - <DbtrAcct>
    - <Id>
      <IBAN>FR7630056000260026200140363</IBAN> 
      </Id>
      </DbtrAcct>
      </DrctDbtTxInf>

    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

    Ed
    • Marked as answer by Ed_Secretan Wednesday, June 13, 2012 10:54 AM
    Wednesday, June 13, 2012 8:47 AM

All replies

  • 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 1:31 PM
  • Tom

    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?

    Thanks again

    Ed

    Thursday, May 31, 2012 2:53 PM
  • Hi Ed,

     

    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

    http://msdn.microsoft.com/en-us/library/aa167823(v=office.11).aspx

     

    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?

     

    Thanks!


    Best Regards,
    Dan F - Microsoft Online Community Support

    Monday, June 4, 2012 8:35 PM
  • Dan

    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;
    - <DrctDbtTxInf>
    - <PmtId>
      <EndToEndId>48538.49665</EndToEndId> 
      </PmtId>
      <InstdAmt Ccy="EUR">229.51</InstdAmt> 
    - <DrctDbtTx>
    - <MndtRltdInf>
      <MndtId>0005363</MndtId> 
      <DtOfSgntr>2011-03-01</DtOfSgntr> 
      <AmdmntInd>false</AmdmntInd> 
      </MndtRltdInf>
    - <CdtrSchmeId>
    - <Id>
    - <PrvtId>
    - <OthrId>
      <Id>GB26ZZZSDDNWBK94894701</Id> 
      <IdTp>SEPA</IdTp> 
      </OthrId>
      </PrvtId>
      </Id>
      </CdtrSchmeId>
      </DrctDbtTx>
    - <DbtrAgt>
    - <FinInstnId>
      <BIC>CCFRFRPP</BIC> 
      </FinInstnId>
      </DbtrAgt>
    - <Dbtr>
      <Nm>AVANTAGES SERVICES</Nm> 
    - <PstlAdr>
      <AdrLine>278 avenue Roland Garros</AdrLine> 
      <AdrLine>BP 413</AdrLine> 
      <Ctry>FR</Ctry> 
      </PstlAdr>
      </Dbtr>
    - <DbtrAcct>
    - <Id>
      <IBAN>FR7630056000260026200140363</IBAN> 
      </Id>
      </DbtrAcct>
      </DrctDbtTxInf>

    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

    Ed
    • Marked as answer by Ed_Secretan Wednesday, June 13, 2012 10:54 AM
    Wednesday, June 13, 2012 8:47 AM
  • Hi Dan.

    If you have to create really complicated Xml documents there is no other way but using DOM object

     http://msdn.microsoft.com/en-us/library/windows/desktop/ms766487(v=vs.85).aspx

    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:21 AM
  • Hi Masig

    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.

    Thanks again.

    Ed

    Wednesday, June 13, 2012 10:54 AM