FOR XML AUTO, ELEMENTS

SELECT ... FOR XML AUTO, ELEMENTS returns a blob
My buisinessappl. can't retrieve a blob from a storedprocedure
Is there anyway i can convert the result in the storedprocedure to a text or
varchar
before returning it to my Buisinessappl.
Or maybe there is a property in MSSQL SERVER that i can change to fix this
JensAre you using SQL Server 2000 or 2005?
Can you change the client code to get the stream back if you are using SQL
Server 2000?
Best regards
Michael
"Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
> SELECT ... FOR XML AUTO, ELEMENTS returns a blob
> My buisinessappl. can't retrieve a blob from a storedprocedure
> Is there anyway i can convert the result in the storedprocedure to a text
> or
> varchar
> before returning it to my Buisinessappl.
> Or maybe there is a property in MSSQL SERVER that i can change to fix this
> Jens|||I'm using SQL Server 2000 and on the clientside a appl built with
PowerBuilder 10.
Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
retrieves one row from the StoredProcedure but the one column that should
contain the XML is empty.
I need to somehow convert the result, varchar(32766) will do fine.
Is it possible to save the result from a SELECT .. FOR XML AUTO statement in
the database
regards
Jens
"Michael Rys [MSFT]" skrev:

> Are you using SQL Server 2000 or 2005?
> Can you change the client code to get the stream back if you are using SQL
> Server 2000?
> Best regards
> Michael
> "Jens Mardh" <Jens Mardh@.discussions.microsoft.com> wrote in message
> news:DF584D00-3AAA-42C2-95F6-E736FE73C92A@.microsoft.com...
>
>|||If you use SQL Server 2000, you have to use the ADO/OLEDB ICommandStream
interface to get the FOR XML result back as a stream and not a rowset.
And there is no easy, performant way to assign the result of a FOR XML query
to a variable or column in SQL Server 2000. You would have to upgrade to SQL
Server 2005 to get this functionality.
Best regards
Michael
"Jens Mardh" <JensMardh@.discussions.microsoft.com> wrote in message
news:E54AEC3C-3783-4930-8F34-4C782FC72471@.microsoft.com...
> I'm using SQL Server 2000 and on the clientside a appl built with
> PowerBuilder 10.
> Using ODBC connection the PB.appl works fine, but using OLE DB the PB.appl
> retrieves one row from the StoredProcedure but the one column that should
> contain the XML is empty.
> I need to somehow convert the result, varchar(32766) will do fine.
> Is it possible to save the result from a SELECT .. FOR XML AUTO statement
> in
> the database
> regards
> Jens
> "Michael Rys [MSFT]" skrev:
>