Select Statement Gets byte[] and string but not XML

Question

In a C# app I am pulling a message off of a SQL service broker queue with the below statement.

When attempting to convert the message_body to SqlBytes and other types an exception is thrown. At runtime message_body always seems to begin as type byte[].

Leaving message_body as a byte[] works but I get an exception complaining of an error in my XML document when trying to deserialize to type SccmAction.

My ultimate goal is to deserialize the message_body in whatever form into an interface in my application.

RECEIVE TOP (1)
        conversation_handle as ConversationHandle
       ,message_body
       ,message_body as MessageBody
       ,convert(xml, message_body) as MessageBodyXml
   FROM [dbo].[MY_QUEUE_SubmitQueue]

---

using (SqlConnection connection =
    new SqlConnection(ConnectionString))
{
    SqlCommand command = new SqlCommand(ReceiveString, connection);
    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        SqlBytes sb = (SqlBytes)reader["message_body"];
        SccmAction sa = DeserializeObject<SccmAction>(sb);
        IDelivery iD = DeserializeObject<IDelivery>(sb);
    }

    reader.Close();
}

public static T DeserializeObject<T>(byte[] ba)
{
    XmlSerializer xs = new XmlSerializer(typeof(T));
    MemoryStream memoryStream = new MemoryStream(ba);
    XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
    return (T)xs.Deserialize(memoryStream);
}

---

<SccmAction>
  <MachineName>Godzilla</MachineName>
  <CollectionName>cn324321</CollectionName>
  <Action>Install</Action>
  <EntryDateTime>Jul 17 2009 12:15PM</EntryDateTime>
</SccmAction>

Edit: Additional Info

Queue Definition

CREATE MESSAGE TYPE [MY_MSG_MessageType] AUTHORIZATION 
[dbo] VALIDATION = WELL_FORMED_XML 

CREATE CONTRACT [MY_CONTRACT_Contract] AUTHORIZATION [dbo] 
([MY_MSG_MessageType] SENT BY INITIATOR) 

CREATE QUEUE [dbo].[MY_QUEUE_SubmitQueue] WITH STATUS = ON , 
RETENTION = OFF ON [PRIMARY] 

CREATE QUEUE [dbo].[MY_QUEUE_ResponseQueue] WITH STATUS = ON , 
RETENTION = OFF ON [PRIMARY] 

CREATE SERVICE [MY_QUEUE_SubmitService] AUTHORIZATION [dbo] 
ON QUEUE [dbo].MY_QUEUE_SubmitQueue([MY_CONTRACT_Contract]) 

CREATE SERVICE [MY_QUEUE_ResponseService] AUTHORIZATION [dbo] ON 
QUEUE [dbo].[MY_QUEUE_ResponseQueue]([DEFAULT])

Exceptions

Type of value has a mismatch with column type Couldn't store
<<SccmAction> 
<MachineName>Godzilla</MachineName><CollectionName>cn324321</CollectionName> 
<Action>Install</Action> 
<EntryDateTime>Jul 17 2009 12:15PM</EntryDateTime> 
</SccmAction>> in MessageBodyXml Column. Expected type is SqlXml.

Unable to cast object of type 'System.Byte[]' to type 'System.Data.SqlTypes.SqlBytes'.

"There is an error in XML document (1, 165)."

Message as it is placed on the queue

'<?xml version="1.0" encoding="utf-8"?>
  <SccmAction>
    <MachineName>' + @machine_name + '</MachineName>
    <CollectionName>' + @collection_name + '</CollectionName>
    <Action>' + @action + '</Action>
    <EntryDateTime>' + CAST(getdate() AS VARCHAR(100)) + '</EntryDateTime>
  </SccmAction>'

Eventual Object I'd like to Deserialize to

public class SccmAction : IDelivery
{
    public string MachineName { get; set; }
    public string CollectionName { get; set; }
    public string Action { get; set; }
    public DateTime EntryDateTime { get; set; }

    public void Deliver()
    {
        throw new NotImplementedException();
    }
}

public interface IDelivery
{
    void Deliver();
}

Answer 1

Don't cast the VARBINARY(MAX) message_body to XML in the RECEIVE statement itself. Its a bad practice because it can trick Service Broker into believing the RECEIVE never occurred if an XML validation exception occurs during the CAST. See the link for details.

I recommend you leave the column as VARBINARY(MAX) (ie. RECEIVE ..., message_body FROM ...) in the projection list and in your C# client consume the column as a SqlBytes, not as a byte[]. You can then leverage the SqlBytes.Stream to construct an XmlReader on top of this stream. Or you can read directly into an XmlDocument.

Don't use strongly typed DataTables with RECEIVE, it does not match the table semantics expected by the Visual Studio builders and you'll cause yourself needless pain. Use a SqlDataReader.

Update

Don't build the sent XML manually from string patches. Use the SQL Server itself to build the XML, via the FOR XML clause (usually using PATH), and assign the result to an XML variable which you SEND:

declare @machine_name sysname
   , @collection_name sysname
   , @action sysname;
select @machine_name = 'Ice Cream'
    , @collection_name = 'Baseball Cards'
    , @action = 'open';

declare @x xml;

select @x = (
   select @machine_name as MachineName
       , @collection_name as CollectionName
       , @action as Action
       , getdate() as EntryDateTime
   for xml path('SccmAction'), type);

send on conversation @h message type [MyMessageType] (@x);

You don't have to worry about XML date formats, UTF encoding or any of that stuff.

Answer 2

I stuck with the byte[] as SqlBytes did not seem to work.

Finally, the issue with deserializing the XML to an object had to do with the value in EntryDateTime not being in ISO 8601 format. By doing a convert I was able to get the date in the appropriate format.

select CONVERT(varchar(30), GETDATE(), 126)