Devart Forums

I'm struggling to render an Oracle select result set as XML
Below is the code I'm using which fails at the line:
Dim myxml As Devart.Data.Oracle.OracleXml = reader.GetOracleXml(1)
The error that I receive is:
Cannot convert object of type 'Devart.Data.Oracle.OracleString' to object of type 'Devart.Data.Oracle.NativeOracleObjectBase'.

I've looked for a good example on the Devart Site and borrowed from the sample but am missing something. The connection appears fine and the reader shows that it has rows before it fails.

Any help?

Protected Sub test3()
Dim cn As New OracleConnection
Dim cmd As New OracleCommand
cmd.CommandText = "SELECT code,name FROM ag_client order by code"
cn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("EMS").ConnectionString
cn.Open()
cmd.Connection = cn

Dim reader As OracleDataReader = cmd.ExecuteReader
Try
If reader.Read Then
>>>error here
Dim myxml As Devart.Data.Oracle.OracleXml = reader.GetOracleXml(1)
End If
Catch
Finally
reader.Close()
End Try
End Sub

jrogerssw wrote:cmd.CommandText = "SELECT code,name FROM ag_client order by code"

The first field in your select statement is 'code' and the second field is 'name'. So, reader.GetOracleXml(1) should correspond to the 'name' field. As the data type of this field is string, you get the corresponding exception. To get the required field, you should use reader.GetOracleXml(0).
In this situation, to avoid confusion you should better use the string type index.
Try using the following code inside the If block:

Thanks for the reply, I've tried
Dim myxml As Devart.Data.Oracle.OracleXml = reader.GetOracleXml("code") and receive the same error.

I've also tried to utilize the code example from XMLTypeDemoControl.vb in the samples
to read the data creating an xmldoc and receive the same error at the Dim oracleXml As oracleXml = reader.GetOracleXml(1) statement

I am not quite understanding the relationship between the datareader and the oraclexml object. My assumption is that reader.GetOracleXML consumes the entire result set into an OracleXML object but maybe I'm not understanding this correctly.

My ultimate goal is to understand how to read the results of Oracle select statements into a dataset.datatable as well as into an XML document. I have searched the documentation for a good example of utilizing OracleXML, but have not found one that is used this way. Do you have a code sample that accomplishes this?

jrogerssw wrote:My assumption is that reader.GetOracleXML consumes the entire result set into an OracleXML object

The OracleXml object represents XML data in Oracle database. Values of the OracleXml type can be retrieved from OracleDataReader and passed to the OracleParameter object. For more information, please refer to http://www.devart.com/dotconnect/oracle ... leXml.html

jrogerssw wrote:My ultimate goal is to understand how to read the results of Oracle select statements into a dataset.datatable as well as into an XML document.

Use the following code: For more information, please refer to http://www.devart.com/dotconnect/oracle ... ument.html

jrogerssw wrote:Thanks for the reply, I've tried
Dim myxml As Devart.Data.Oracle.OracleXml = reader.GetOracleXml("code") and receive the same error.

Could you please send us a small test project with the corresponding DDL/DML scripts for reproducing the issue?