OLEDB consumer and for xml SELECT

Hi,
I have a select statement which retrieve data in xml format (FOR XML
AUTO option). When I run this statement from a client using an OLEDB
consumer template for the table, I get the data BUT it does not look
right... Here is a sample:
suppose I run the following statement:
SELECT StateID,
RTRIM(StateCode) AS StateCode,
RTRIM(StateName) as StateName,
RTRIM(Country) as Country
FROM State
FOR XML AUTO
This sql will generate the following result if run from SQL Query Analyzer:
..........................................
<State StateID="1" StateCode="AL" StateName="Alabama" Country="USA"/>
<State StateID="2" StateCode="AK" StateName="Alaska" Country="USA"/>
<State StateID="3" StateCode="AZ" StateName="Arizona" Country="USA"/>
........... etc.
When I run the same query from a client using an OLEDB consumer template, I
get a string with a lot of nulls, the xml format is no longer there, there
are some unprintable chars, etc. The odd thing is that the data is there! It
just is not in the right format!?
Does anyone know what is going on?
Thanks,
George.Did you use the CommandStream interface? This looks like the binary format
that is being returned if you use the rowset interface. Using the
CommandStream interface will be giving you the stream in parseable XML.
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:uNWVeiwCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a select statement which retrieve data in xml format (FOR XML
> AUTO option). When I run this statement from a client using an OLEDB
> consumer template for the table, I get the data BUT it does not look
> right... Here is a sample:
> suppose I run the following statement:
> SELECT StateID,
> RTRIM(StateCode) AS StateCode,
> RTRIM(StateName) as StateName,
> RTRIM(Country) as Country
> FROM State
> FOR XML AUTO
> This sql will generate the following result if run from SQL Query
> Analyzer:
> ..........................................
> <State StateID="1" StateCode="AL" StateName="Alabama" Country="USA"/>
> <State StateID="2" StateCode="AK" StateName="Alaska" Country="USA"/>
> <State StateID="3" StateCode="AZ" StateName="Arizona" Country="USA"/>
> ........... etc.
> When I run the same query from a client using an OLEDB consumer template,
> I get a string with a lot of nulls, the xml format is no longer there,
> there are some unprintable chars, etc. The odd thing is that the data is
> there! It just is not in the right format!?
> Does anyone know what is going on?
> Thanks,
> George.
>|||Michael,
Thanks. No I did not use ICommandStream. The database access is done
using a stored procedure, and the OLEDB client code is generated by the
wizard. That creates a class ready to run the stored procedure and return
the result set.
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23ULojEUDFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Did you use the CommandStream interface? This looks like the binary format
> that is being returned if you use the rowset interface. Using the
> CommandStream interface will be giving you the stream in parseable XML.
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:uNWVeiwCFHA.2568@.TK2MSFTNGP10.phx.gbl...
>|||I assume that this is the problem. If the stored proc generates a FOR XML
result, your OLEDB code has to use the command stream and not the normal way
of retrieving a relational rowset. FOR XML results are generating an XML
stream and not a rowset after all...
The Books Online should have some sample code snippets.
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:%23pMpqvWDFHA.2620@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks. No I did not use ICommandStream. The database access is done
> using a stored procedure, and the OLEDB client code is generated by the
> wizard. That creates a class ready to run the stored procedure and return
> the result set.
> George.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23ULojEUDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||Michael,
Thanks. Here is an answer I got in oledb forum:
.........................................................
...................
Yes, there is something happening in OLE DB. Query Analyzer uses ODBC,
so there is no problem.
You would see the problem if you did:
SELECT * FROM OPENQUERY(LOOPBACK, 'SELECT * FROM tbl FOR XML AUTO')
And LOOPBACK is a linked server set up with SQLOLEDB.
In SQL 2005, there is a new command-line tool SQLCMD which is implemented
with SQL Native Client (SQLOLEDB for SQL 2005). And sure enough, if you
issue a FOR XML query, all you get is a bunch of hex digits. I've submitted
a bug report for that. I wonder how they will fix it...
.........................................................
.........................................................
.........
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:OVjWxBoDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>I assume that this is the problem. If the stored proc generates a FOR XML
>result, your OLEDB code has to use the command stream and not the normal
>way of retrieving a relational rowset. FOR XML results are generating an
>XML stream and not a rowset after all...
> The Books Online should have some sample code snippets.
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:%23pMpqvWDFHA.2620@.tk2msftngp13.phx.gbl...
>|||Correct. But please note that the OPENQUERY always requests a rowset and not
a CommandStream.
If you code against OLEDB yourself, you can use the ICommandStream and get
the XML back as a nice XML character stream. Were you able to try that?
Best regards
Michael
"George Tihenea" <tihenea@.comcast.net> wrote in message
news:eIEqR5tDFHA.1040@.TK2MSFTNGP09.phx.gbl...
> Michael,
> Thanks. Here is an answer I got in oledb forum:
> ........................................................
....................
> Yes, there is something happening in OLE DB. Query Analyzer uses ODBC,
> so there is no problem.
> You would see the problem if you did:
> SELECT * FROM OPENQUERY(LOOPBACK, 'SELECT * FROM tbl FOR XML AUTO')
> And LOOPBACK is a linked server set up with SQLOLEDB.
> In SQL 2005, there is a new command-line tool SQLCMD which is implemented
> with SQL Native Client (SQLOLEDB for SQL 2005). And sure enough, if you
> issue a FOR XML query, all you get is a bunch of hex digits. I've
> submitted
> a bug report for that. I wonder how they will fix it...
> ........................................................
.........................................................
..........
> George.
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:OVjWxBoDFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||Michael,
Thanks. I am using the class created by the OLEDB wizard to add the
consumer template. That has an ICommandStream and I can read the data but it
is the same. Do you have a sample somewhere showing how to use
ICommandStream with a class generated by the wizard?
George.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ea$6uuyDFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Correct. But please note that the OPENQUERY always requests a rowset and
> not a CommandStream.
> If you code against OLEDB yourself, you can use the ICommandStream and get
> the XML back as a nice XML character stream. Were you able to try that?
> Best regards
> Michael
> "George Tihenea" <tihenea@.comcast.net> wrote in message
> news:eIEqR5tDFHA.1040@.TK2MSFTNGP09.phx.gbl...
>