Importing XML through .NET Web Service with VBA

[brazilnut]

I need some guidance regarding importing XML data through .NET Web
Services using VBA.

I currently have a spreadsheet (Excel v 2003) that utilizes external
data. I currently use ADODB to connect to and retrieve data from my
databases. These import procedures are written in VBA code modules and
are invoked through various forms and buttons throughout the
spreadsheet. These databases are located on a SQL Server (v 5.1 I
think). I have local ODBC datasources setup on my PC to access the
data. And the queries are written in stored procedures. The problem is
that I can only use the spreadsheet on a PC with access to the SQL
server. My solution is to setup a .NET Web Service that makes the calls
to the DB and returns an XML result set to the calling app (or client
application). The difficulty I am having is creating the procedure in
VBA to open, read, and copy the XML data from the web service to a
range of cells in my spreadsheet.

This is what I have so far:

Here is the WebMethod I have setup on the web service:

Code:
--------------------
‘ convert an adodb recordset into xml and return the a string of xml
Public Function ConvertADODBRecordset2XmlString(ByRef rst As ADODB.Recordset) As String
Dim oDomDoc As New Interop.MSXML5.DOMDocument40Class
rst.Save(oDomDoc, ADODB.PersistFormatEnum.adPersistXML)
Return oDomDoc.xml
End Function
--------------------


Here is the code I have in my VBA code module:

Code:
--------------------
Public Sub GetData()
Dim objSClient As MSSOAPLib30.SoapClient30
Dim oXML As MSXML2.DOMDocument40

' Point the SOAP API to the web service that we want to call...
Set objSClient = New SoapClient30
Call objSClient.mssoapinit(par_WSDLFile:="http://localhost/WebService/WebService.wsdl")

'create new empty XML document
Set oXML = New DOMDocument40

' Call the web service and get requested XML document
Call oXML.LoadXml(objSClient.ConvertADODBRecordset2XmlString (var1, var2, var3))

' copy xml data to sheet in Excel
???WHAT CODE DO I USE HERE????

Set oXML = Nothing
Set objSClient = Nothing

End Sub
--------------------


Before I was using the Range(“A1”).CopyFromRecordset rst method.
This worked GREAT. Very fast and easy to use. The code I have now seems
to work fine. I simply don’t know of a good way to get the XML data in
the cells, like I did with ADODB. Does this make sense?

Thank you.

PS – I have considered converting the xml back into a recordset and
then use the .CopyFromRecordset procedure. But I don’t know how slow
this would make the app. The speed of the data imports is very fast. I
would like to keep it that way.

 

[Tim Williams]

Since you already have a viable approach (open the XML directly as a
recordset and use copyfrom recordset), why not just test that? It seems to
be the most direct method and there seems to reason to expect it would be
slower than any other....

The other approach would require you to "manually" parse the returned XML
doc and write the values to the sheet or contruct an intermediate array and
write that in one shot. Seems like a lot more work.

Tim.

 

[brazilnut]

Thanks for the reply. Do you have any code that demonstrates how t
"open the XML directly as a recordset and use copyfrom recordset"? Tha
would be extrememly helpful.

Also, when you return a string type from the web service, i
automaticall puts string tags around my xml file data like this
<string>... all my xml ... </string>. I am having some trouble parsin
the XML, could this be the cause? Do I need to strip out the <string
tag? I tried to return a type of XMLDocument... but I then did not kno
how to handle the object when it was reuturned from the WebService. An
ideas?

Thanks again for the prompt reply

 

[Tim Williams]

Seemed you already had code for copyfromrecordset.

To open an XML-persited recordset try this:
http://msdn.microsoft.com/library/d...do270/htm/mdconpersistingrecordsxmlformat.asp

Tim

"brazilnut" <[email protected]>
wrote in message
news:[email protected]...

 

[brazilnut]

Thank you very much. This is exactly what I needed.

 

[brazilnut]

I'm having some trouble. My web method returns a string of XML. What i
the best way to load that xml string into the recordset? I can't see
to figure it out.

Thanks

 

[brazilnut]

For those who may find it useful, I have figured it out. Here is th
algorithm:


Code
-------------------
Public Sub GetXML()

Dim objSClient As MSSOAPLib30.SoapClient30 ' soap object to access and expose web service interface
Dim oXML As MSXML2.DOMDocument40 ' xml document object
Dim rst As New ADODB.Recordset ' ADODB recordset
Dim str As New ADODB.Stream ' ADODB stream

' create new soap client
Set objSClient = New SoapClient30

'create new empty XML document
Set oXML = New DOMDocument40

' Point the SOAP API to the web service that we want to call...
' the wsdl file contains the scheme for the webservice
Call objSClient.mssoapinit(par_WSDLFile:="http://localhost/WebService.wsdl")

' Call the web service and load requested XML document in to MSXML DOM document structure
Call oXML.LoadXml(objSClient.GetXMLString())

' open sream
str.Open

' save xml document from web service to stream
oXML.Save str

' set starting position to beginning of stream
str.Position = 0

' open recordset from stream
rst.Open str

' copy recordset to range of cells
Range("A1").CopyFromRecordset rst

Set oXML = Nothing
Set objSClient = Nothing
Set rst = Nothing
Set stream = Nothing

End Su