Message Types are the first of the types of objects
that we will be looking at. The Message Type is simply a definition of
the type of data that will be contained within the message. When you
create a Message Type you define the type of validation that the SQL
SERVICE BROKER will be performing on the body of the message. You have
four options for this validation. They are NONE, EMPTY, WELL_FORMED_XML,
and VALID_XML WITH SCHEMA COLLECTION, with NONE being the default. As
with all other database objects Message Types are created under the
ownership of a specific database user or database role. Although it is
most common to create the objects under the ownership of dbo, any valid
database user or role can be specified.
Tip
Make sure that you know the rules for each of the validation options of the MESSAGE TYPES.
Message Types are created using the CREATE MESSAGE TYPE
command. Like all the other objects within the SQL SERVICE BROKER
Message Type, objects are database specific. The name of the Message
Type can be up to 128 characters in length. Although you can use any
object name for any SQL SERVICE BROKER objects, since the messages can
be sent between systems great care should be used when naming objects so
as to ensure that there are no naming collisions when sending messages
between servers. One technique is to use a UNC style naming convention,
which helps keep SERVICE BROKER object names from colliding with each
other when you start sending messages between servers.
The validation type NONE is
used when you wish to put any type of data within the message body. This
includes text, numbers, XML, or binary data. When using this validation
type, you can send any data you like within the message body, except
for a NULL value. If you want to ensure that an empty message is sent,
you have to specify the validation type EMPTY.
The validation type
WELL_FORMED_XML requires that the data you are inserting into the
message is correctly formed XML. This saves you from having to write
your own validation logic at the receiving side of the message to ensure
that the data is a valid XML document.
The validation type
VALID_XML WITH SCHEMA COLLECTION requires not only that your XML data be
valid, but that it meets the requirements of the predefined XML schema
collection. The XML schema collection should be an already defined XML
schema definition created within the SQL Server database. Although the
creation of XML schemas is outside the scope of this article, you can
read more about it by referencing Books OnLine under the index heading
“CREATE XML SCHEMA COLLECTION statement.” When using this validation
type, before sending the message SQL SERVICE BROKER will first check
that the XML document you are sending fits the required XML schema
definition; if it does the message will be sent. If the message does not
fit the XML schema definition the message will not be sent, and an
error message will be returned to the calling code.
The actual syntax for creating a Message Type is very simple and straightforward.
CREATE MESSAGE TYPE [YourApplication/YourMessageType]
AUTHORIZATION dbo
VALIDATION = NONE;
As you can see in the sample code, the name of the Message Type is YourApplication/YourMessageType. The Message Type will be owned by dbo,
and there will be no validation performed. I most often use the
validation of NONE or EMPTY because I prefer to have my own logic on the
receiving side handle checking if the XML is correct. I also do not
want the SQL Server engine to have to spend extra time checking that the
XML fits the official definition of “well-formed XML.”
If you wish to change the validation after you create the Message Type you can alter the Message Type by using the ALTER MESSAGE TYPE command. The authorization cannot be changed after the Message Type has been created.
Exercise . Creating Message Types
Create
four message types within the AdventureWorks database. Use the
validation NONE for two of them and WELL_FORMED_XML for the other two.
Create the Message Types based on the data shown in Table 1.
Table 1. Message Types and Their Validations
| Message Type Name | Message Type Validation |
|---|
| MT _None | NONE |
| MT _XML | WELL_FORMED_XML |
