Results 1 to 8 of 8

Thread: [RESOLVED] export query from Ms Access into XML using vb6?

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2021
    Posts
    10

    Resolved [RESOLVED] export query from Ms Access into XML using vb6?

    Hi All,

    I have a requirement to export data from an existing MS Access query into XML using VB6 which I will then use to update a wordpress site with the addon WP All Imports.

    Test code below seems to work fine however when I try importing the XML it is appearing as one row even though the preview in XL shows a perfectly formatted XML file with multiple rows!

    Any ideas what I might be missing or could try differently? Another ways to do this?

    thanks, Jay


    Code:
    Private Sub test_Click()
           
        Dim RS As ADODB.Recordset
        Set RS = CreateObject("ADODB.Recordset")
        
        With RS
        .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbSourceFile & ";Jet OLEDB:Database Password=12345!"
        .Source = ("ExportQuery")
        .Open
        End With
        
        CustomSaveXML RS, "sample2.xml"
        RS.Close
             
            
    End Sub
    Code:
    Private Sub CustomSaveXML( _
        ByVal Recordset As ADODB.Recordset, _
        ByVal FilePath As String)
        Dim Stream As IUnknown
        Dim HRESULT As HRESULT
        Dim Attributes As SAXAttributes60
        Dim Writer As MSXML2.MXXMLWriter60
        Dim Handler As MSXML2.IVBSAXContentHandler
        Dim Field As ADODB.Field
        Dim StringValue As String
    
        Set Stream = Nothing 'Force creation on 64-bit Windows.  Not sure why
                             'this is required or why it works.
        HRESULT = SHCreateStreamOnFile(StrPtr(FilePath), _
                                       STGM_CREATE _
                                    Or STGM_WRITE _
                                    Or STGM_SHARE_EXCLUSIVE, _
                                       Stream)
        If HRESULT <> S_OK Then
            Err.Raise &H80044900, _
                      "CustomSaveXML", _
                      "SHCreateStreamOnFile error " & Hex$(HRESULT)
        End If
        Set Attributes = New MSXML2.SAXAttributes60
        Set Writer = New MSXML2.MXXMLWriter60
        Set Handler = Writer
        With Writer
            .omitXMLDeclaration = True
            .standalone = True
            .disableOutputEscaping = False
            .indent = True
            .encoding = "utf-8"
            .output = Stream
        End With
        With Handler
            .startDocument
            .startElement "", "", "data", Attributes
            Do Until Recordset.EOF
                With Attributes
                    For Each Field In Recordset.Fields
                        Select Case VarType(Field.Value)
                            Case vbNull
                                'Force as empty String:
                                StringValue = ""
                            Case vbString
                                StringValue = Field.Value
                            Case Else
                                'This converts to a String value using the
                                'Invariant Locale:
                                StringValue = LTrim$(Str$(Field.Value))
                        End Select
                        .addAttribute "", "", Field.Name, "", StringValue
                    Next
                End With
                .startElement "", "", "row", Attributes
                .endElement "", "", "row"
                Attributes.Clear
                Recordset.MoveNext
            Loop
            .endElement "", "", "data"
            .endDocument
        End With
    End Sub

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    23,461

    Re: export query from Ms Access into XML using vb6?

    I have no idea what "WP All Imports" expects, but saying "XML" doesn't tell us anything. There is always a schema involved, whether formal or implied, just as for CSV or JSON or any other serialized text format.

    Does the expected XML layout even use XML attributes for the data items at all? Maybe it wants child nodes within each row node instead.

    I'm not sure we can help much from this distance without asking a painfully long list of questions to drag the problem out of you bit by bit. Is there a programmer there you can have this discussion with?

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,805

    Re: export query from Ms Access into XML using vb6?

    Test code below seems to work fine however when I try importing the XML it is appearing as one row even though the preview in XL shows a perfectly formatted XML file with multiple rows!
    Sounds like newlines are being stripped... ok, fine... that's not important ... what's important is if the data is actually there in the XML ... so is it? If it is, then there's no problem. That's part of the "joy" of XML is that format is irrelevant - semantics is what's king here. As long as the tags are proper and in the correct spot, it doesn't matter if it's all on one line or "prettified" ... it's not for human consumption any how. You're not the one reading it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2021
    Posts
    10

    Re: export query from Ms Access into XML using vb6?

    Quote Originally Posted by dilettante View Post
    I have no idea what "WP All Imports" expects, but saying "XML" doesn't tell us anything. There is always a schema involved, whether formal or implied, just as for CSV or JSON or any other serialized text format.

    Does the expected XML layout even use XML attributes for the data items at all? Maybe it wants child nodes within each row node instead.

    I'm not sure we can help much from this distance without asking a painfully long list of questions to drag the problem out of you bit by bit. Is there a programmer there you can have this discussion with?
    Hi there and thank you for taking the time to respond!

    Here is some more detail on the requirement ... if I right click on the query in MS Access and export to XML - the XML upload into wordpress works like a dream! I am trying to replicate this function in VB6 to automate the process and limit user intervention.

    This bit code is getting me closer to have a properly formatted xml file however following your reply if I compare the two I can see that the XML export from Access and the one from Vb6 have different XML maps.


    Code:
    Set db = New ADODB.Connection
        db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbSourceFile
    
        Set rs = New ADODB.Recordset
        rs.Open szQuery, db, adOpenStatic, adLockOptimistic
        rs.Save "test.xml", adPersistXML
    can i add some code to use the xml schema?

    thanks again.

    jay

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    23,461

    Re: export query from Ms Access into XML using vb6?

    No, you can't just use the ADO Recordset's XML persistence format.


    From a few searches, it looks like MS Access craps out exported XML in the bulky "child node" form as I suggested earlier. We can bare-bones that as follows, and perhaps get something similar:

    Code:
    Private Sub CustomSaveXML( _
        ByVal Recordset As ADODB.Recordset, _
        ByVal FilePath As String)
        Dim Stream As IUnknown
        Dim HRESULT As HRESULT
        Dim Attributes As SAXAttributes60
        Dim Writer As MSXML2.MXXMLWriter60
        Dim Handler As MSXML2.IVBSAXContentHandler
        Dim Field As ADODB.Field
        Dim StringValue As String
        
        Set Stream = Nothing
        HRESULT = SHCreateStreamOnFile(StrPtr(FilePath), _
                                       STGM_CREATE _
                                    Or STGM_WRITE _
                                    Or STGM_SHARE_EXCLUSIVE, _
                                       Stream)
        If HRESULT <> S_OK Then
            Err.Raise &H80044900, _
                      "CustomSaveXML", _
                      "SHCreateStreamOnFile error " & Hex$(HRESULT)
        End If
        Set Attributes = New MSXML2.SAXAttributes60
        Set Writer = New MSXML2.MXXMLWriter60
        Set Handler = Writer
        With Writer
            .omitXMLDeclaration = True
            .standalone = True
            .disableOutputEscaping = False
            .indent = True
            .encoding = "utf-8"
            .output = Stream
        End With
        With Handler
            .startDocument
            .startElement "", "", "data", Attributes
                Do Until Recordset.EOF
                    .startElement "", "", "row", Attributes
                    For Each Field In Recordset.Fields
                        Select Case VarType(Field.Value)
                            Case vbNull
                                'Force as empty String:
                                StringValue = ""
                            Case vbString
                                StringValue = Field.Value
                            Case Else
                                'This converts to a String value using the
                                'Invariant Locale:
                                StringValue = LTrim$(Str$(Field.Value))
                        End Select
                        .startElement "", "", Field.Name, Attributes
                        .characters StringValue
                        .endElement "", "", Field.Name
                    Next
                    .endElement "", "", "row"
                    Recordset.MoveNext
                Loop
            .endElement "", "", "data"
            .endDocument
        End With
    End Sub
    Whether this imports for you is another question. MS Access also uses a namspace: xmlns:od="urn:schemas-microsoft-com:officedata" which your importing software may ignore or may require.

    MS Access also uses a top-level node of <dataRoot/> to encapsulate the actual row nodes from what I can see. No idea whether that is needed, and for that matter I have no idea whether your importing software requires specific name formats for the document and row nodes.


    This isn't the sort of thing you do through copy and paste. You're supposed to know what you are doing. Is there a programmer nearby who can help you?

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2021
    Posts
    10

    Re: export query from Ms Access into XML using vb6?

    Gulp!

    I copy/pasted your tweaks into the vb6 project and it worked straight out of the box!!! I did update the import proggy to look for "row" and it worked like a dream.

    Thank you so much for your help - honestly this is my 3rd week (<- not day) at it.

    Sending good karma ...

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    23,461

    Re: [RESOLVED] export query from Ms Access into XML using vb6?

    Whew, glad it helps. These often turn into days and days of posts back and forth until useful results are obtained. Good luck!

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    23,461

    Re: [RESOLVED] export query from Ms Access into XML using vb6?

    Also keep in mind that the code above makes assumptions. One is that you are only dealing with Null, String, or simple numeric data types. Stuff like Boolean, binary (Byte array), or arbitrary String data needs more effort at encoding and may require CDATA wrapper sections in the output XML.

    For example, characters such as "<", ">", and "&" aren't legal XML text.

    You may need the IVBSAXLexicalHandler interface of the MXXMLWriter60 object as well as its IVBSAXContentHandler interface in order to do this.

    Code:
    Private Sub CustomSaveXML( _
        ByVal Recordset As ADODB.Recordset, _
        ByVal FilePath As String)
        Dim Stream As IUnknown
        Dim HRESULT As HRESULT
        Dim Attributes As SAXAttributes60
        Dim Writer As MSXML2.MXXMLWriter60
        Dim CHandler As MSXML2.IVBSAXContentHandler
        Dim LHandler As MSXML2.IVBSAXLexicalHandler
        Dim Field As ADODB.Field
        Dim StringValue As String
        Dim WriteCDATA As Boolean
        
        Set Stream = Nothing
        HRESULT = SHCreateStreamOnFile(StrPtr(FilePath), _
                                       STGM_CREATE _
                                    Or STGM_WRITE _
                                    Or STGM_SHARE_EXCLUSIVE, _
                                       Stream)
        If HRESULT <> S_OK Then
            Err.Raise &H80044900, _
                      "CustomSaveXML", _
                      "SHCreateStreamOnFile error " & Hex$(HRESULT)
        End If
        Set Attributes = New MSXML2.SAXAttributes60
        Set Writer = New MSXML2.MXXMLWriter60
        Set CHandler = Writer
        Set LHandler = Writer
        With Writer
            .omitXMLDeclaration = True
            .standalone = True
            .disableOutputEscaping = False
            .indent = True
            .encoding = "utf-8"
            .output = Stream
        End With
        With CHandler
            .startDocument
            .startElement "", "", "data", Attributes
                Do Until Recordset.EOF
                    .startElement "", "", "row", Attributes
                    For Each Field In Recordset.Fields
                        Select Case VarType(Field.Value)
                            Case vbNull
                                'Force as empty String:
                                StringValue = ""
                            Case vbString
                                StringValue = Field.Value
                                WriteCDATA = True '<-- Assume we might have illegal characters.
                            Case Else
                                'This converts to a String value using the
                                'Invariant Locale:
                                StringValue = LTrim$(Str$(Field.Value))
                        End Select
                        .startElement "", "", Field.Name, Attributes
                        If WriteCDATA Then LHandler.startCDATA
                        .characters StringValue
                        If WriteCDATA Then
                            LHandler.endCDATA
                            WriteCDATA = False
                        End If
                        .endElement "", "", Field.Name
                    Next
                    .endElement "", "", "row"
                    Recordset.MoveNext
                Loop
            .endElement "", "", "data"
            .endDocument
        End With
    End Sub
    Result for my testdata:

    Code:
    <data>
    	<row>
    		<LongVal>65536</LongVal>
    		<StringVal>
    			<![CDATA[Fred]]>
    		</StringVal>
    		<DoubleVal>3.5</DoubleVal>
    	</row>
    	<row>
    		<LongVal></LongVal>
    		<StringVal>
    			<![CDATA[Has-Null]]>
    		</StringVal>
    		<DoubleVal>1.23456</DoubleVal>
    	</row>
    	<row>
    		<LongVal>65537</LongVal>
    		<StringVal>
    			<![CDATA[Barney]]>
    		</StringVal>
    		<DoubleVal>37</DoubleVal>
    	</row>
    </data>
    XML is not as simple as people like to think it is.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width