exec linkedserver.dbo.forxml_proc: why do I receive "binar..

External Since: Jan 11, 2008 Posts: 13

Hi,

I guess this is an old topic and I also tried to get an answer in
the ...xml group but to no avail - sorry for the repost. I have a
stored procedure "xmlproc" on "sqlserver_A", which returns xml via a
"select ... for xml auto" query. "sqlserver_B" is linked to
sqlserver_A (I tried to link as "SQL Server", "Native Client" and
"OLEDB") and calls

exec sqlserver_A.mydb.dbo.xmlproc

But instead of returning xml data, I receive a binary string that
looks like

0x440C690064005F0050006F0072007...

Is there anyway around this?

TIA, Regards
DC

External Since: May 30, 2004 Posts: 2061

DC (dc@upsize.de) writes:
> I guess this is an old topic and I also tried to get an answer in
> the ...xml group but to no avail - sorry for the repost. I have a
> stored procedure "xmlproc" on "sqlserver_A", which returns xml via a
> "select ... for xml auto" query. "sqlserver_B" is linked to
> sqlserver_A (I tried to link as "SQL Server", "Native Client" and
> "OLEDB") and calls
>
> exec sqlserver_A.mydb.dbo.xmlproc
>
> But instead of returning xml data, I receive a binary string that
> looks like
>
> 0x440C690064005F0050006F0072007...
>
> Is there anyway around this?

First why this happens: when FOR XML data is returned through OLE DB,
it is transmitted in a binary format which is more compact than the
text version. The client is assumed to be able to unpack this, and
indeed you can do this in an OLE DB client. However, the application
needs to know that FOR XML data is coming and use different API calls.
In this situation, the client is the calling SQL Server. But there is
no way to tell SQL Server "Hey, I will call a remote SP that emits FOR
XML, so do it right, will you?". (A similar problem exists with SQLCMD:
in SQLCMD if you use FOR XML you will just get a binary back. However,
SQLCMD has a mode switch to deal with the situation.)

When researching alternatives, I found that this was a little more
difficult that I thought. The first attempt was to convert the returned
binary data to xml as suggested by Rubén. No, it is not that simple.
The next idea was to rewrite the procedure to have an output parameter
of the XML datatype. Alas, it is not supported to have output parameter
of LOB types (xml, varchar(MAX) etc) in remote procedure calls.
Eventually I arrived at this solution:

create procedure nisse as
declare @x varbinary(MAX)
set @x = convert(varbinary(MAX),
(select * from sys.objects for xml auto, type))
select @x

That is, add ", type" to the FOR XML clause, convert this to varbinary(MAX)
and return this. On the calling side:

create table #hh(a varbinary(MAX) NOT NULL)
insert #hh(a)
exec NOV07.tempdb.dbo.nisse
select convert(xml, a) from #hh

Use INSERT-EXEC to capture it and convert back to XML. Why the road over
varbinary(MAX)? Because XML is neither supported as result column in
remote queries.

The problem with this solution is that it requires you to rewrite the
procedure you call. If you cannot do this, I think your best option
is to write a CLR stored procedure that connects to the remote server,
possibly using the XmlReader to get the data. (It's not something I've
done myself, so I cannot offer any samples.)

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx