Load XML from ADO Recordset

[Mac]

Hi all

I have a asp.net application which calls a web service
via a wdsl file which retrieves an XML String.

The XML String is from a adodb recordset saved as via a
stream.

Dim oRS as New ADODB.Recordset
Dim oStream as New ADODB.Stream
Set oRS = oCnn.Execute(sSQL)
'Save recordset as XML
oRS.Save oStream, adPersistXML
sXML = oStream.ReadText

In the ASP.Net application I retrieve the sXML via the
web service without any problems but when I try and load
to an XML document, nothing is loaded.

Dim xmlDoc As New XmlDocument
xmlDoc.LoadXml(sXML)

The xmlDoc remains empty.

I have been scratching my head on this for a couple of
hours and I am sure it's probably simple but would
appreciate any help.

Regards

mac

 

[Ken Cox [Microsoft MVP]]

Hi Mac,

Could you give us a little more detail or code?

It would help to have a sample of the string that you are getting into sXML
to analyze why the XML document doesn't like it. I assume that the XML is
well-formed.

How did you test that xmlDoc was empty?

Thanks,

Ken

 

[Guest]

Hi Ken

Thanks for your reply

I have attached examples of the code and xml that is
produced.

Any help would be really appreciated.

The XML is produced by a VB6 dll on a remote server using
the following code:

Public Function ReturnData() as String
Dim oCnn As New ADODB.Connection
Dim oStream As New ADODB.Stream
Dim oRS As New ADODB.Recordset

oCnn.ConnectionString = SQL_CONNECTION_STRING
oCnn.Open

oRS.CursorLocation = adUseClient
Set oRS = oCnn.Execute("SELECT .....")

'Save recordset as XML
oRS.Save oStream, adPersistXML

ReturnData = oStream.ReadText

Set oStream = Nothing
Set oRS = Nothing
Set oCnn = Nothing


End Function

The XML string is as follows

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-
00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'
rs:CommandTimeout='30'>
<s:AttributeType name='SUN_REGION'
rs:number='1'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='4'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='SUN_DB'
rs:number='2'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='3'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='AMOUNT'
rs:number='3' rs:nullable='true'
rs:writeunknown='true'>
<s:datatype dt:type='number'
rs:dbtype='numeric' dt:maxLength='19'
rs:scale='3' rsrecision='38'
rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='ACCNT_CODE'
rs:number='4' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A0'
rs:number='5' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A1'
rs:number='6' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A2'
rs:number='7' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A3'
rs:number='8' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A4'
rs:number='9' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A5'
rs:number='10' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A6'
rs:number='11' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A7'
rs:number='12' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A8'
rs:number='13' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_A9'
rs:number='14' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='ANAL_T0'
rs:number='15' rs:writeunknown='true'>
<s:datatype dt:type='string'
rs:dbtype='str' dt:maxLength='15'
rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row SUN_REGION='EMEA' SUN_DB='ZZZ'
AMOUNT='1155.000'
ACCNT_CODE='1050 '
ANAL_A0=' '
ANAL_A1=' '
ANAL_A2=' ' ANAL_A3='BS140 '
ANAL_A4=' '
ANAL_A5=' ' ANAL_A6=' '
ANAL_A7=' '
ANAL_A8=' ' ANAL_A9=' '
/>
</rs:data>
</xml>

In the ASP.Net app, I have registered the web service and
in the function call it as follows

Dim sXML as String
Dim xmlDoc As XmlDocument
Dim oWeb As New SoapEMEA.SunLink
sXML = oWeb.ReturnData()
'Load XML string into XML Doc
xmlDoc.LoadXml(sXML)

Now I could be really stupid (my girlfriend thinks so)
but I have tested to see if the xmlDoc has any data by
looking at xmlDoc.value.

As far as I can tell, it hasn't been loaded.

Help?

Regards

Ross

 

[Ken Cox [Microsoft MVP]]

Hi Mac,

I was able to emit the XML you provided via a Web service (as a string),
capture it into an XmlDocument and confirm that the XML is in there. I wrote
it to the Output/Debug window.

Here's the code I used:

Private Sub Page_Load _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
Dim strXML As String
Dim xmlws As New xmlsrver
strXML = xmlws.ServeXML
Dim xmlDoc As New XmlDocument
xmlDoc.LoadXml(strXML)
Debug.Write(xmlDoc.OuterXml)
End Sub

 

[Ken Cox [Microsoft MVP]]

Also, I tried it with and without the XML prologue and it worked.

<?xml version="1.0" encoding="utf-8" ?>

 

[Mac]

Hi Ken

Thanks for your reply. If anything has come out of this
is that I now know how to check an XML document in .net.

Thanks for your time, it is appreciated

Mac