MS SQL XML - Getting XML via an OLEDB connection

Author

Getting XML via an OLEDB connection

Antonio

2005-12-22, 8:25 pm

Hi all.

I've a problem trying to get XML from SQL Server via an OleDb
Connection.

More precisely, the code does not work if the stored have the "FOR XML"
clause, otherwise works fine.

As you can see from the code, after the ExecuteReader, row by row
(while reading), I put the object array representing the fields into an
array of object.

The array will be parsed to return data back to the client in some
other format. As said, this code works fine if the stored are
"regular". When a stored returns XML (in UTF-16 Encoding) I'm able to
get the byte[] from the datareader into the usual object[], but I'm not
able to convert that byte array into the corresponding string. The
conversion does not work for UTF-16, UTF-8, ....

Any idea about that?

thanks and ciao
Antonio

Well, this is the code I'm using to get the data is:

OleDbConnection _DB = new OleDbConnection();
_DB.ConnectionString = strConnString;
_DB.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText ="sp_FORXML";
cmd.Connection = _DB;

//... add parameter here [...omissis...]

OleDbDataReader dr;
dr = cmd.ExecuteReader(cb);
int arrayindex;
arrayindex = 0;

while (dr.Read())
{
Row r = new Row();
object[] vals = new object[dr.FieldCount];
dr.GetValues(vals);
r._Fields = vals;
values. Insert(arrayindex++,
r);
}
dr.Close();
_DB.Close();

Row[] aRowToRet = new Row[values.Count];
values. CopyTo(aRowToRet,0);

return aRowToRet;

Michael Rys [MSFT]

2005-12-23, 3:24 am

You need to use the command stream interfaces of OLEDB and ADO to get the
XML back. If you are using ADO.Net, use the ExecuteXMLReader...

Please consult the documentation that contains some examples.

Best regards
Michael

"Antonio" <adi_mitri@hotmail.com> wrote in message
news:1135289008.238814.202850@z14g2000cwz.googlegroups.com...
> Hi all.
>
> I've a problem trying to get XML from SQL Server via an OleDb
> Connection.
>
> More precisely, the code does not work if the stored have the "FOR XML"
> clause, otherwise works fine.
>
> As you can see from the code, after the ExecuteReader, row by row
> (while reading), I put the object array representing the fields into an
> array of object.
>
> The array will be parsed to return data back to the client in some
> other format. As said, this code works fine if the stored are
> "regular". When a stored returns XML (in UTF-16 Encoding) I'm able to
> get the byte[] from the datareader into the usual object[], but I'm not
> able to convert that byte array into the corresponding string. The
> conversion does not work for UTF-16, UTF-8, ....
>
> Any idea about that?
>
> thanks and ciao
> Antonio
>
>
> Well, this is the code I'm using to get the data is:
>
> OleDbConnection _DB = new OleDbConnection();
> _DB.ConnectionString = strConnString;
> _DB.Open();
> OleDbCommand cmd = new OleDbCommand();
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.CommandText ="sp_FORXML";
> cmd.Connection = _DB;
>
> //... add parameter here [...omissis...]
>
> OleDbDataReader dr;
> dr = cmd.ExecuteReader(cb);
> int arrayindex;
> arrayindex = 0;
>
> while (dr.Read())
> {
> Row r = new Row();
> object[] vals = new object[dr.FieldCount];
> dr.GetValues(vals);
> r._Fields = vals;
> values. Insert(arrayindex++,
r);
> }
> dr.Close();
> _DB.Close();
>
> Row[] aRowToRet = new Row[values.Count];
> values. CopyTo(aRowToRet,0);

> return aRowToRet;
>

Antonio

2005-12-23, 3:24 am

Thanks Michael,

any pointer to the documentation? I'm searching and searching.....

I'm using SQL Server 2005 + VS.NET 2005

Thanks in advance
Antonio

Han

2005-12-24, 3:23 am

I think SqlClient is bettern than OleDb. Try,

static string strcon = "Data Source=(local); Initial
Catalog=db_name; Integrated Security=True;";
static void Main(string[] args)
{
SqlConnection con = new SqlConnection(strcon
);
con.Open();
SqlCommand cmd = new SqlCommand("select 1 for xml path", con);
cmd.CommandType = CommandType.Text;
XmlReader xr = cmd.ExecuteXmlReader();
xr.MoveToContent();
Console.WriteLine(xr.ReadOuterXml());
}

Namespaces are,

using System.Data;
using System.Data.SqlClient;
using System.Xml;

--
Pohwan Han. Seoul. Have a nice day.
"Antonio" <adi_mitri@hotmail.com> wrote in message
news:1135325332.709943.214990@g43g2000cwa.googlegroups.com...
> Thanks Michael,
>
> any pointer to the documentation? I'm searching and searching.....
>
> I'm using SQL Server 2005 + VS.NET 2005
>
> Thanks in advance
> Antonio
>

Antonio

2005-12-25, 7:23 am

Thanks Han,

OLEDB I's not an option, I'm re-engeneering an already existing
application.

Can anybody show some code snippet about using OleDB treating xml data
deriving from a "FOR XML" stored procedure?

thanks a lot,
Antonio

Roger Wolter[MSFT]

2005-12-25, 8:23 pm

Here's an example from books on line:

Option Explicit
Sub main()
Dim oTestStream As New ADODB.Stream
Dim oTestConnection As New ADODB.Connection
Dim oTestCommand As New ADODB.Command

oTestConnection.Open " provider=SQLXMLOLEDB
.4.0;data provider=SQLNCLI;dat
a
source=SqlServerName
;initial catalog=AdventureWor
ks;Integrated Security=SSPI
;"
oTestCommand.ActiveConnection = oTestConnection
oTestCommand.Properties("ClientSideXML") = True
oTestCommand.CommandText = "SELECT TOP 10 FirstName, LastName FROM
Person.Contact FOR XML AUTO"
oTestStream.Open
oTestCommand.Properties("Output Stream").Value = oTestStream
oTestCommand.Properties("xml root") = "root"
oTestCommand.Execute , , adExecuteStream

oTestStream.Position = 0
oTestStream.Charset = "utf-8"
Debug.Print oTestStream.ReadText(adReadAll)
End Sub
Sub Form_Load()
main
End Sub
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Antonio" <adi_mitri@hotmail.com> wrote in message
news:1135505428.922213.273920@g14g2000cwa.googlegroups.com...
> Thanks Han,
>
> OLEDB I's not an option, I'm re-engeneering an already existing
> application.
>
> Can anybody show some code snippet about using OleDB treating xml data
> deriving from a "FOR XML" stored procedure?
>
> thanks a lot,
> Antonio
>

Antonio

2006-01-04, 3:23 am

Thanks Roger, but I meant using System.Data.OleDb namespace.

Dushan Bilbija

2006-01-10, 8:24 pm

hi roger

what about if you have a stored proc that returns 2 datasets? using a
recordset, i use nextrecordset to get the 2nd dataset. can't do that with a
command object.... or at least with one that writes to a stream. can you?

dushan bilbija

"Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
news:O4z7VpbCGHA.2908@TK2MSFTNGP09.phx.gbl...
> Here's an example from books on line:
>
> Option Explicit
> Sub main()
> Dim oTestStream As New ADODB.Stream
> Dim oTestConnection As New ADODB.Connection
> Dim oTestCommand As New ADODB.Command
>
> oTestConnection.Open " provider=SQLXMLOLEDB
.4.0;data provider=SQLNCLI;dat
a
> source=SqlServerName
;initial catalog=AdventureWor
ks;Integrated
> Security=SSPI ;"
> oTestCommand.ActiveConnection = oTestConnection
> oTestCommand.Properties("ClientSideXML") = True
> oTestCommand.CommandText = "SELECT TOP 10 FirstName, LastName FROM
> Person.Contact FOR XML AUTO"
> oTestStream.Open
> oTestCommand.Properties("Output Stream").Value = oTestStream
> oTestCommand.Properties("xml root") = "root"
> oTestCommand.Execute , , adExecuteStream
>
> oTestStream.Position = 0
> oTestStream.Charset = "utf-8"
> Debug.Print oTestStream.ReadText(adReadAll)
> End Sub
> Sub Form_Load()
> main
> End Sub
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Antonio" <adi_mitri@hotmail.com> wrote in message
> news:1135505428.922213.273920@g14g2000cwa.googlegroups.com...
>
>

Roger Wolter[MSFT]

2006-01-11, 3:23 am

No.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Dushan Bilbija" <dbilbija@msn.com> wrote in message
news:e2gnx%23iFGHA.3532@TK2MSFTNGP14.phx.gbl...
> hi roger
>
> what about if you have a stored proc that returns 2 datasets? using a
> recordset, i use nextrecordset to get the 2nd dataset. can't do that with
> a command object.... or at least with one that writes to a stream. can
> you?
>
> dushan bilbija
>
> "Roger Wolter[MSFT]" <rwolter@online.microsoft.com> wrote in message
> news:O4z7VpbCGHA.2908@TK2MSFTNGP09.phx.gbl...
>
>

Sponsored Links