CE's Blog

Returning XML data over a Linked Server connection in SQL Server 2005/2008 is not supported in general. However there are several workarounds for this issue which I would like to discuss in this post.

Recently we have encountered a major issue within an application that was supposed to retrieve XML data through a linked server. When the application was set up to connect and retrieve XML data directly from the SQL Server (2008 R2), the result was as expected. However when we executed the same stored procedure over a linked server connection, we were only able to retrieve binary data from the same call instead of proper XML.

Nothing wrong with the linked server though, since the standard “SQL Server” provider was used between both instances of SQL Server 2008 R2.
To reproduce the issue, it is not even necessary to have a second SQL Server instance:

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc out', @optvalue=N'true'

This is all it takes to set up a new linked server which points at the local SQL Server instance. The result of the following two queries is very diffferent:

EXEC           master.dbo.sp_executesql N'SELECT 255 AS A FOR XML RAW'
EXEC LOCALHOST.master.dbo.sp_executesql N'SELECT 255 AS A FOR XML RAW'

We would expect that both would return a single XML resultset in SQL Server Management Studio. Here is the result:

Result of the second query:

0x44014100440372006F0077000102020142FF00000043

Obviously the data returned from the second query is in binary format, always starting with 0×44. However it is not just an XML or NVARCHAR data type which was cast to XML, so any attempts to recover the string representation of the XML file by means of CAST or CONVERT will fail. It turns out that the linked server is not able to decode the SQL Server binary serialized XML format which is used for passing XML data over the TDS protocol to clients. Instead, this data is interpreted as VARBINARY and therefore fails to convert into text or xml on the client side.

Interestingly, the issue does not exist if we are using a very old SQL Server provider which is using ODBC over OLEDB in 32 bit mode. It is really not recommended to use this provider for setting up a linked server in a 64-bit production environment, so I won’t go into details here on how to configure it.

If it is an option to change the code which is executed over a linked server connection, the easiest solution is to return XML data as NVARCHAR instead of in native XML format.

SELECT 255 AS A FOR XML RAW

So the above query can be rewritten to a more or less equivalent:

WITH CTE( X ) AS ( SELECT 255 AS A FOR XML RAW ) SELECT CONVERT( NVARCHAR(MAX), X ) AS X FROM CTE

Result:

Microsoft SQL Server product support has confirmed the above limitation of the linked server provider and suggested a reengineering of the system to get rid of linked server connections in general, which may not be an option for everyone running into this issue. It was also confirmed that it is not possible to convert the binary serialized XML back into textual representation by means of .NET functionality within the Microsoft.SqlServer namespace.

Not all is lost though, since there is a relatively old SQL Server XML API called SQLXML (based on COM) which is capable of converting binary serialized XML data back into its string representation. The API does not ship as part of the .NET framework. Although it also contains managed wrapper classes so it is not necessary to directly use COM interop for using the API in .NET. The latest version can be downloaded here:

Unfortunately, SQLXML does not provide a simple conversion function which allows us to pass in an array of binary serialized XML and retrieve an object oriented or textual representation of its content. The conversion logic can only be used when retrieving binary serialized XML data from the server. So the only way to convert the data is to make a round trip to a SQL Server instance in binary form and apply the conversion logic to the result.
Here is an example:

public string ConvertBinaryToXmlString( string sColumnName, byte[] data )
{
	if ( sColumnName != null )
		sColumnName = sColumnName.Replace( "]", "" ); 
	string sQuery = String.Format( "SELECT ? AS [{0}]", sColumnName );
	SqlXmlCommand cmd = new SqlXmlCommand( this.ConnectionString );
	cmd.CreateParameter().Value = data;
	cmd.CommandType = SqlXmlCommandType.Sql;
	cmd.CommandText = sQuery;
	using ( System.IO.Stream s = cmd.ExecuteStream() )
	using ( StreamReader r = new StreamReader( s ) )
		return r.ReadToEnd();
}

The above is not sufficient for converting XML data of arbitrary length into an XML string. As a matter of fact, it will only work with XML data when the binary representation is smaller than approximately 2 KiB.

In order to convert larger resultsets which contain XML data, we have to add more logic to the actual retrieval of the binary serialized XML.

The System.Data.SqlClient namespace does not include a straightforward way of sending an arbitrary amount of data rows to SQL Server in a single statement, e.g. for making a mass updates/bulk insert.

In most circumstances, generating SQL statements dynamically using string concatenation would be a bad choice, since it might offer vectors for SQL injection and other pitfalls resulting from inadequately escaped code.

When using SQL Server 2008, table valued parameters come in handy, however they require modifications on the server which may not be eligible in all scenarios.

There is another possibility using SQL Server’s builtin XML data type together with the System.Xml classes to achieve this goal. The following code demonstrates a simple approach for inserting data from a string array using a single SQL statement.

using ( SqlConnection con = new SqlConnection( "connection string" ) )
{
    con.Open();
    using ( SqlCommand cmd = con.CreateCommand() )
    {
        string[] arr = { "a", "b", "c", "d" };
        XmlDocument xml = new XmlDocument();
        xml.AppendChild( xml.CreateElement( "MyColumns" ) );
        foreach ( string s in arr )
        {
            XmlElement e = xml.CreateElement( "MyColumn" );
            e.InnerText = s;
            xml.DocumentElement.AppendChild( e );
	}
 
        cmd.AddParameter( "@XML", DbType.Xml, xml.OuterXml );
        cmd.CommandText = 
            "INSERT INTO MyTable ( SomeColumn ) SELECT Item.value( '.', 'NVARCHAR(MAX)' ) " + 
            "FROM @XML.nodes( 'MyColumns/MyColumn' ) AS x( Item );";
        cmd.ExecuteNonQuery();
    }
}

Since preparing data with an XmlDocument can require a lot of memory with large data, the following alternative can be considered when passing a string array:

/// <summary>Serializes the given strings to an XML string. Will use short element names to reduce string size.</summary>
public static string SerializeToXML( IEnumerable<string> strings )
{
    using ( StringWriter sw = new StringWriter() )
    using ( XmlTextWriter xw = new XmlTextWriter( sw ) )
    {
        foreach ( string s in strings )
        {
            xw.WriteStartElement( "s" );
            xw.WriteString( s );
            xw.WriteEndElement();
        }
        xw.Flush();
        return sw.ToString();
    }
}

When an entire object needs to be passed to the database, the following generic solution works well:

/// <summary>Serializes the given objects to an XML string. Will use XmlSerializer which requires more memory.</summary>
public static string SerializeToXMLUsingXmlSerializer<T>( IEnumerable<T> t )
{
    using ( StringWriter sw = new StringWriter() )
    using ( XmlWriter xw = XmlWriter.Create( sw, new XmlWriterSettings() { OmitXmlDeclaration = true, Indent = false } ) )
    {
        T[] tmp = t.ToArray();
        XmlSerializer xs = new XmlSerializer( tmp.GetType(), new XmlRootAttribute( typeof( T ).Name ) );
        XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
        ns.Add( String.Empty, String.Empty );
        xs.Serialize( xw, tmp, ns );
        return sw.ToString();
    }
}

When only sending a few rows to the server, this approach does not offer better performance that an iterative approach. However when dealing with larger amounts of data, the overhead of sending, executing, receiving and evaluating each query will add up to a signifficant amount of time – which can be avoided using this technique.