query to XML string

[Carl Witte]

Hi. I'm working with a web service who requests data uploaded as an xml string with xmlhttp "POST" command. I've got the data in a query that I want to send. Unfortunately it doesn't appear thier protocols are going to allow a file upload, that would be nice and easy. Is there an easy way (short of manually forming the xml with VBA code) from the data in the query? I'm working in MSAccess 07.

[nico5038]

Did you check the DoCmd.Output command ?

Nic;o)

[Carl Witte]

Unfortunately, (presuming you mean the DoCmd.OutputTo command as I don't have a docmd.output) that appears to only send to a file... If I wanted to go to a file I'd generally use "ExportXML." I need to go to a string. Are you recommending I export to a file and load the file into a string?

[nico5038]

Yes, using the FileSystemObject a simple .ReadAll will get all into a string.
The next code shows a functon importing multiple files into multiple rows:

Function fncImportFiles(strPath As String)
 
Dim rs As DAO.Recordset
'This function will import files from the passed path into table "tblFileImport" with a memo field
'Make sure the strPath has a trailing "\"
If Right(strPath, 1) <> "\" Then
   strPath = strPath & "\"
End If
    Set rs = CurrentDb.OpenRecordset("tblFileImport")
    Dim fs, f, f1, fc, s, tso
    Set fs = CreateObject("Scripting.FileSystemObject")
    ' file directory
    Set f = fs.GetFolder(strPath)
    ' file collection
    Set fc = f.Files
    For Each f1 In fc
        Set tso = fs.OpenTextFile(strPath & f1.Name, 1, False, 0)
        rs.AddNew
        rs!filecontent = tso.ReadAll
        rs.Update
    Next
 
End Function
 

Getting the idea ?

Nc;o)

[Carl Witte]

I think so. I'll play with it and see if I can figure it out. Thanks for your help. Do you mind if I let you know if I have a problem?

[nico5038]

No problem, we're here to help :-)

Nic;o)

[ADezii]

Yes, using the FileSystemObject a simple .ReadAll will get all into a string.
The next code shows a functon importing multiple files into multiple rows:

Function fncImportFiles(strPath As String)
 
Dim rs As DAO.Recordset
'This function will import files from the passed path into table "tblFileImport" with a memo field
'Make sure the strPath has a trailing "\"
If Right(strPath, 1) <> "\" Then
   strPath = strPath & "\"
End If
    Set rs = CurrentDb.OpenRecordset("tblFileImport")
    Dim fs, f, f1, fc, s, tso
    Set fs = CreateObject("Scripting.FileSystemObject")
    ' file directory
    Set f = fs.GetFolder(strPath)
    ' file collection
    Set fc = f.Files
    For Each f1 In fc
        Set tso = fs.OpenTextFile(strPath & f1.Name, 1, False, 0)
        rs.AddNew
        rs!filecontent = tso.ReadAll
        rs.Update
    Next
 
End Function
 

Getting the idea ?

Nc;o)

Hello Nico, just a simple question to settle my curiosity. In Line #10, why you Declare all Variables as Variants as opposed to Declaring them as Explicit Data Types?

[nico5038]

That's how the MS sample from the helpfile does declare the variables, I was lazy :-)

Nic;o)

[NeoPa]

:D - But what's Microsoft's excuse?

[nico5038]

:D - But what's Microsoft's excuse?

1) Lazyness and/or not interested in providing us with sound "typed" samples.
2) They have been pressed by the sales dept to create a helpfile in a too short timeframe.

Guess 2 is closest...

Nic;o)