SQL Server2005 XML data type Base article

First, the introduction

Today, in SQL Server 2005, XML is a first-class data type. With strongly typed support based on XML schemas and server-side XML data validation, now

, developers can easily modify the stored XML documents remotely. As a database developer, many people have to involve XML in large numbers.

Today, in SQL Server 2005, you can store XML in a database in the form of a new data type.

In fact, some of the XML features are already included in SQL Server 2000. Among them, the most critical feature is the use of the FOR XML statement to return the results in XML form. SQL Server 2005

Features are significantly different. In SQL Server 2005, XML is a true data type; This means that you can use XML as a column in tables and views, and XML can be used in T-SQL language

In a sentence or as a parameter to a stored procedure. Now you can store, query, and manage XML files directly in the database.

More importantly, you can now also specify the patterns that your XML must follow.

In SQL Server 2005, in addition to providing a mechanism to validate the XML types in your database, it also allows you to describe the complex data types to be stored and provide an engine to

Force the imposition of these rules.

ii. Use of XML data types

In fact, there is no fundamental difference between the XML data type and other data types in SQL Server. You can use it in any normal SQL data type place. For example, the following

Statement to create an XML variable and populate it with an XML:

DECLARE @doc XML

SELECT @doc = ' '

Alternatively, you can populate an XML variable with a query and SQL Server FOR XML syntax:

SELECT @doc =

(SELECT * from Person.Contact for XML AUTO)

XML data types can be used not only as variables, but also in table columns. You can also assign default values and support NOT NULL constraints:

The following are the referenced contents: CREATE TABLE Team ( Teamid int identity NOT NULL, Teamdoc XML DEFAULT ' ' not NULL )

Note: SQL Server 2005 has an obvious difference in XML functionality from SQL Server 2000.

Inserting XML data into a table only needs to be specified in the form of a string of XML.

The following example inserts a set of records:

The following are the referenced contents: INSERT into the team (Teamdoc) VALUES (' Role= "Closer"/> '); INSERT into the team (Teamdoc) VALUES (' Role= "Starter"/> ');

When you create an instance of XML in SQL Server 2005, the only conversion is from a string to an XML type. Again, along the opposite direction, you can only put the XML type

Converted to a string type. Converting between text and ntext types is not allowed.

III. Limitations of XML data Types

Although XML data types are treated like many other data types in SQL Server 2005, there are specific limitations on how to use them. These restrictions are:

· XML types cannot be converted to text or ntext data types.

· No other data type can be converted to XML except for the string type.

· XML columns cannot be applied to the group by statement.

· A distributed local (partitioned) view cannot contain an XML data type.

· The use of sql_variant instances cannot be used as a subtype of XML.

· An XML column cannot be part of a primary key or a foreign key.

· The XML column cannot be specified as unique.

· The COLLATE clause cannot be used on an XML column.

· XML columns cannot be added to the rule.

· The only built-in scalar functions that can be applied to an XML column are IsNull and coalesce. There are no other built-in scalar functions that support the use of XML types.

· You can have up to 32 XML columns in a table.

· A table with an XML column cannot have a primary key that has more than 15 columns.

· A table with an XML column cannot have a timestamp data type as part of their primary key.

· The XML stored in the database supports only level 128 levels.

Iv. XML Type Methods

So far, the example above shows that the XML data type can only be used as a blob type of data, but this is where the XML data type shows its own power. XML data type

supports several methods of calling using the UDT point (myxml.operation ()) syntax. The supported methods are listed in table 1 below.

Table 1:xml Data type method.

Method Name Description

Query executes an XML query and returns the results of the query

exists executes an XML query and returns a value of 1 if there is a result

Value evaluates a query to return a simple value from the XML

Modify perform a modification operation in the appropriate location of the XML document

nodes allows you to decompose XML into a table structure

In the following sections, you use a table team that contains the name of a group in each row. In each row, there is a teamdoc that contains XML data about the group

Yes:

The following are the referenced contents: CREATE TABLE Team ( Teamid int identity NOT NULL, Teamdoc XML DEFAULT ' ' not NULL ) In these examples, we assume that the following XML document exists in the Braves row in the table: Role= "Starter" bats= "switch"/>

Query Method

Note: You can use XML in table and view columns, T-SQL statements, or parameters in stored procedures.

This method allows you to specify an XQuery or XPath expression to compute, and the result of the method is an XML data type object. Its specific grammatical form is as follows:

Query (XQuery)

The first argument is always an XQuery expression. The following example uses a query to return an XML document-which contains information about each team's pitcher:

SELECT teamdoc.query ('/team/players/pitcher ')

From Team

This statement produces the following result:

The following are the referenced contents: ---------------------------------------------- (1 row (s) affected)

This query method allows you to query and return a list of nodes that match the expression you specify. The true power of this method comes from the XQuery syntax, which we will detail later in this article

On.

exist method

This exist method is used to determine whether a query can produce any results. The grammatical form of this exist method is as follows:

exist (XQuery)

When you use this exist method, it computes the XQuery query and returns a value of 1 if the query produces any results. For example, the following statement queries whether the Group table row

Starter pitchers exist in the Teamdoc domain:

The following is a simple exist statement:

The following are the referenced contents: SELECT Count (*) From Team WHERE Teamdoc.exist ( '/team/players/pitcher[@role = ' Starter '] = 1

Total 2 page: previous 1 [2] Next page