At the heart of SQL Server’s support for XML lies the XML data type, which lets you store XML data in database objects such as variables, columns, and parameters. When you configure one of these objects with the XML data type, you simply specify the type name as you would any other SQL Server type. What sets the XML data type apart from the other types are a number of features that affect the way you can store, query, modify, and index XML data—all concepts we’ll be covering as we progress through this Stairway series.

The XML data type ensures that your XML data is well formed, that is, conforms to ISO standards. You can use the data type to store either XML documents or fragments. As you’ll recall from Level 1, an XML document is one that has a single top-level, or root, element. Without a single root element, the XML is considered a fragment.

Before you define an object with the XML data type, you should be aware that it has several limitations, including the following:

• An instance of an XML column cannot exceed 2 GB.
• An XML column cannot be an index key.
• You cannot use an XML object in a GROUP BY clause.
• You cannot compare or sort data that uses the XML type.

Even if you can work within these limitations, there are times when using the XML data type isn’t necessary. For example, if you’re simply storing your XML documents in their entirety and don’t plan to query or modify individual XML components, you should consider using large object storage instead, such as VARCHAR(MAX). That way, you’re not invoking the XML parser, which helps to minimize the overhead necessary to store your XML documents. However, if you want to take advantage of the capabilities specific to the XML data type, then by all means, use it when defining your variables, columns, and parameters.

Defining an XML Variable

As mentioned above, to define a database object with the XML data type, you specify the type as you would any other SQL Server type. In the case of variables, simply provide the variable name, followed by the XML type, as shown in the following Transact-SQL code:

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
SELECT @ClientList
GO

The example begins by using a DECLARE statement to define the @ClientList variable. When I declare the variable, I simply include the XML data type name after the variable name.

I then use the SET statement to set the value of the variable to equal a small but well formed XML document. The first line of the document is the declaration, which specifies the XML version and encoding. The next line is a comment about the nature of the content in the XML document. The SQL Server XML parser essentially ignores any commented information. The rest of the document is the actual XML, which contains the root element <People> and two instances of the <Person> child element.

NOTE: The components that make up an XML document are described in Level 1 of the Stairway to XML series. If you’re not familiar with these XML components and have not yet reviewed the first Level, you might benefit from reading that one first, before continuing on with this Level.

After I set the variable’s value, I use a SELECT statement to retrieve the value. As you would expect, the statement returns the XML document, as shown in the following results:

<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>

That’s all there is to defining an XML variable. Now let’s look at how to define an XML column.

Defining an XML Column

There are no real magic tricks when it comes to creating a database object configured with the XML data type. If you can define an XML variable, you can just as easily define an XML column. In the following example, I create the StoreClients table, which stores an ID and list of client information for each company store:

USE AdventureWorks2008R2
GO
IF OBJECT_ID('dbo.StoreClients') IS NOT NULL
DROP TABLE dbo.StoreClients
GO
CREATE TABLE dbo.StoreClients
(
StoreID INT IDENTITY PRIMARY KEY,
ClientInfo XML NOT NULL
)
GO

The ID, of course, is stored in the StoreID column, which is configured with the INT data type. The client information is stored in an XML column, in this case, ClientInfo. Because I use an XML column, I can take advantage of the extensible nature of XML and store client-related data that does not conform easily to a relational model, while still associating that data with a specific store, as identified in the StoreID column.

When I define the ClientInfo column, I simply specify the XML data type as I would any other type. In fact, at its most basic level, the column is treated just like any other column. As a result, I can include an XML column in a table along with other column types. Each XML document or fragment stored in the XML column is treated as an individual value, just like the values in any other columns, thus preserving the table’s atomic nature. That way, I can build a table with any assortment of columns yet include one or more XML columns.

After I create the StoreClients table, I can insert data into the table, including XML documents and fragments. In the following example, I declare an XML variable and use that variable in an INSERT statement that adds a row of data into the table:

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
INSERT INTO dbo.StoreClients (ClientInfo)
VALUES(@ClientList)
GO

In this case, the INSERT statement adds the XML data to the table’s ClientInfo column. Although the @ClientList variable holds an entire XML document, the INSERT statement treats the document as a single value when adding that document to the ClientInfo column. (The value in the StoreID column is generated automatically because it’s an IDENTITY column.)

If you were to query the table after running the INSERT statement, you would receive the StoreID value (a 1 if this was the first row inserted into the table) and the full XML document from the ClientInfo column.

As you can see, creating an XML column and inserting data into that column is a fairly straightforward process. There are, of course, ways to make this process far more complicated, which you’ll learn how to do as we work through the Stairway series. Until then, know that, at its most basic, working with the XML data type is for the most a painless endeavor. So let’s look at how to create an XML parameter.

Defining an XML Parameter

When creating a stored procedure in SQL Server, you might want to pass data into the procedure when you call it or have the procedure return data after it executes. You can do this by including parameters in your procedure definition. Not surprisingly, you can configure those parameters with the XML data type, as you can variables and columns.

For example, in the following CREATE PROCEDURE statement, I define the @StoreClients input parameter, which is configured with the XML data type:

USE AdventureWorks2008R2
GO
IF OBJECT_ID('dbo.AddClientInfo', 'P') IS NOT NULL
DROP PROCEDURE dbo.AddClientInfo
GO
CREATE PROCEDURE dbo.AddClientInfo
@StoreClients XML
AS
INSERT INTO dbo.StoreClients (ClientInfo)
VALUES(@StoreClients)
GO

As you can see, my parameter definition follows the CREATE PROCEDURE clause. I specify that the parameter be defined with the XML data type by including the data type name after the parameter name, just like I do for columns and variables. Notice that the INSERT statement contained within the procedure definition uses the parameter in its VALUES clause. That means, when you run the AddClientInfo stored procedure, you must specify a value for the @StoreClients input parameter, and that value must be an XML document or fragment so it can be inserted into the ClientInfo column.

For instance, in the following example, I declare the @ClientList variable, assign an XML document to the variable, and then use the variable to provide a value to the @StoreClients input parameter when I call the AddClientInfo stored procedure:

DECLARE @ClientList XML
SET @ClientList =
'<?xml version="1.0" encoding="UTF-8"?>
<!-- A list of current clients -->
<People>
<Person id="1234">
<FirstName>John</FirstName>
<LastName>Doe</LastName>
</Person>
<Person id="5678">
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</People>'
EXEC dbo.AddClientInfo @ClientList

As you saw in the previous examples, the @ClientList variable is defined with the XML data type and set to equal the <People> XML document. Because the @StoreClients parameter is also configured with the XML data type, I can pass in the @ClientList value into the parameter. As a result, when I run the stored procedure, the XML document is added to the StoreClients table. If I were to then query the table, I would find that a second row has been added, with a StoreID value of 2 and a ClientInfo value equal to the XML document I passed into the @ClientList variable.

Summary

As you’ve seen in this Level, configuring a variable, column, or parameter with the XML data type is as easy as configuring one of these objects with any other type. However, as you work through subsequent Levels in the Stairway to XML series, you’ll find that there are additional options available to you when defining an XML object. For instance, you can associate a schema with an XML object to ensure that your XML documents and fragments conform to a more-rigidly defined format. In fact, you’ll learn how to do just that in the next Level. So stand by. More fun is coming your way.