7.1. xml Data Type
The new xml data type supports storing both XML documents and fragments in the database. An XML fragment is an XML instance that does not have a single top-level (root) element. You can create columns, parameters, and variables of the new xml type and store XML instances in them. xml data type instances have a maximum size of 2GB.
An XML schema collection can be associated with a column, parameter, or variable of xml data type. An xml data type with an associated schema is referred to as being typed. The XML schema validates xml data type instances against constraints and provides data type information about the elements and attributes in the instance. The schema also helps SQL Server optimize data storage. The XML schema collection must be registered with SQL Server before it can be used to create typed xml instances. Registration is described in the "Managing XML Schema Collections" section later in this chapter.
If you want to use xml data type query methods against xml data type columns or variables, or want to create or rebuild indexes on XML data type columns, you must set the SQL Server 2005 database configuration options listed in Table 7-1. By default, the values are set as required. They may be changed using the SET statement. You can check the values for each database by executing the following T-SQL query:
SELECT * FROM sys.databases
7.1.1. Creating xml Data Type Columns and Variables
The following subsections describe how to create xml data type columns and T-SQL variables.
Use the CREATE TABLE statement to create a table that contains one or more xml data type columns. The syntax for creating a table with an xml data type column is:
CREATE TABLE table_name ( ... xml_column_name xml [ [DOCUMENT | CONTENT] (schema_name.xml_schema_collection_name ) ], ... )
The following example creates a table named xmlTest that has an untyped xml data type column named xmlCol. The example also creates a clustered primary key on the ID column for use in later examples.
USE ProgrammingSqlServer2005 CREATE TABLE xmlTable ( ID int NOT NULL, xmlCol xml, CONSTRAINT PK_xmlTable PRIMARY KEY CLUSTERED (ID) )
You can query the sys.columns catalog view to get information about the xml data type columns in a database. The following query returns the xml data type columns in a database:
USE AdventureWorks SELECT o.name, c.* FROM sys.columns c JOIN sys.objects o ON c.object_id = o.object_id WHERE EXISTS (SELECT * FROM sys.types t WHERE c.system_type_id = t.system_type_id AND name='xml')
Partial results for running the query against the AdventureWorks database are shown in Figure 7-1.
Figure 7-1. Results for sys.columns catalog view example
This query joins the sys.columns catalog view to the sys.objects catalog view to return the table or view that the xml data type column belongs to as the first column in the result set. The EXISTS clause filters the results to include only xml data types.
The DECLARE statement is used to create T-SQL variables . The syntax for creating an xml data type variable is:
DECLARE variable_name [AS] xml [ ( [ DOCUMENT | CONTENT] schema_name.xml_schema_collection_name ) ]
The other parameters are the same as those discussed in the preceding "Columns" section.
The following example uses an xml data type variable to insert a row into the xmlTable created in the preceding "Columns" section:
USE ProgrammingSqlServer2005 DECLARE @xmlVar xml SET @xmlVar = '<rootNode><childElement/></rootNode>' INSERT INTO xmlTable (ID, xmlCol) VALUES (1, @xmlVar)
The following example creates a stored procedure to modify a row in the xmlTable table:
USE ProgrammingSqlServer2005 GO CREATE PROCEDURE updateXmlTable @ID int, @xmlCol xml AS BEGIN UPDATE xmlTable SET xmlCol = @xmlCol WHERE ID = @ID END
Execute the stored procedure using the following code to update the xmlCol value for the row with ID = 1:
USE ProgrammingSqlServer2005 SELECT * FROM xmlTable EXEC updateXmlTable 1, '<newRootNode><newChildElement/></newRootNode>' SELECT * FROM xmlTable
The before and after result sets returned by the query are shown in Figure 7-2.
Figure 7-2. Result sets for stored procedure using xml data type example
The xml data type has the following limitations :