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

[jaybeegee66]

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

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

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

[dilettante]

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?

[techgnome]

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

[jaybeegee66]

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.

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

[dilettante]

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:

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?

[jaybeegee66]

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 ...

[dilettante]

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

[dilettante]

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.

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:

<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.