XML type in Yukon and binary serialization

SQL Server 2005 has new XML data type. When you store an XML value or you send it over the wire, is not serialized as a text XML representation, but as a binary XML representation.

The System.Data.SqlTypes.SqlXml type as a .CreateReader() method that returns an XmlReader. This means you can pass it to any .NET object that takes an XmlReader without even creating the XML text representation, saving memory and parsing time.

Suppose you want to write a stored procedure that returns a XML type as a ref parameter. You can write:

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StoredProcedureOut(ref SqlXml sqlXml)

{

      string myXml = "<Customer>Peter</Customer>";

      sqlXml = new SqlXml(new XmlTextReader(new StringReader(myXml)));

}

The problem is that when you call that stored procedure from ADO.NET client code you cannot get the result as a SqlXml type. It's returned as a string. If you want to feed it to an object that needs an XmlReader, you need to parse the returned string's XML.

IMHO this is a bad thing, but it looks it's because the ADO.NET team wants to enable non .NET 2.0 clients to invoke the stored procedures.

Now, what if you really want to return a SqlXml from a stored procedure and you don't want to get an XML string?

It can be done by returning a resultset to the client with only one row and one column :

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StoredProcedureResultset()

{

      string myXml = "<Customer>Peter</Customer>";

      SqlXml x = new SqlXml(new XmlTextReader(new StringReader(myXml)));

      SqlDataRecord rec = new SqlDataRecord(new SqlMetaData("xmlType", SqlDbType.Xml));

      SqlContext.Pipe.SendResultsStart(rec);

      rec.SetSqlXml(0, x);

      SqlContext.Pipe.SendResultsRow(rec);

      SqlContext.Pipe.SendResultsEnd();

}

However, if you invoke this stored procedure and ask for the type returned by the server using GetFieldType() you will still receive 'System.String'! But if you read the field using GetSqlXml() then you will, finally, get the SqlXml value.

SqlCommand command1 = new SqlCommand("StoredProcedureResultset", con); command1.CommandType = CommandType.StoredProcedure;

using (SqlDataReader reader = command1.ExecuteReader())

{

      while (reader.Read())

      {

            Type type = reader.GetFieldType(0); // Returns System.String

            SqlXml value = reader.GetSqlXml(0); // It works!

      }

}