The XML type in SQL 2005.

The XML type in SQL 2005.

http://blog.csdn.net/sgear/article/details/7349657

Basics of XML data types for SQL Server 2005
First, Introduction
Today, in SQL Server 2005, XML becomes a classic data type. With strongly typed support based on XML schemas and server-side XML data validation, developers can now easily modify stored XML documents remotely.
As a database developer, many people have to deal with 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 XML features have been included in SQL Server 2000. The most critical feature of these is the use of the FOR XML statement to return the results in XML form. The functionality of SQL Server 2005 is significantly different. In SQL Server 2005, XML is a true data type, which means that you can use XML as a column in tables and views, and XML can be used in T-SQL statements or as parameters of stored procedures. Now you can store, query, and manage XML files directly in the database.

The important thing is that you can now also specify the pattern 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 enforce these rules.

Ii. use of XML data types

In fact, there is no fundamental difference between XML data types and other data types in SQL Server. You can use it wherever you are using any ordinary SQL data type. For example, the following statement creates an XML variable and populates 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)

The XML data type can be used not only as a variable, but also in a table column. You can also assign a default value and support a NOT NULL constraint:
CREATE TABLE Team
(
Teamid int identity NOT NULL,
Teamdoc XML DEFAULT ' ' not NULL
)

Note the XML features of SQL Server 2005 are significantly different from those in SQL Server 2000.

Inserting XML data into a table requires only the XML specified in the form of a string.

The following example inserts a set of records:
INSERT into Team (Teamdoc)
VALUES ('


Role= "Closer"/>

');
INSERT into Team (Teamdoc)
VALUES ('


Role= "Starter"/>

');

When you create an instance of XML in SQL Server 2005, the only conversion is to convert from a string to an XML type. Similarly, in the opposite direction, you can only convert the XML type to a string type. Conversion between the text and ntext types is not allowed.

III. limitations of XML data types

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

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

· In addition to string types, no other data type can be converted to XML.

· An XML column cannot be applied to a GROUP by statement.

· The distributed local (partitioned) view cannot contain XML data types.

· 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 foreign key.

· The XML column cannot be specified as unique.

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

· The XML column cannot be added to the rule.

· The only built-in scalar functions that can be applied to XML columns 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 exceeds 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

To this end, the above example has shown that the XML data type can only be used as a BLOB type data, but this is where the XML data type displays its own power. The XML data type supports several methods of invocation 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 at the appropriate location in the XML document
nodes allows you to decompose XML into a table structure

In the following sections, you will use a table team with each row containing the name of a group. In each row, there is a Teamdoc line that contains XML data about the group:
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 the parameters of table and view columns, T-SQL statements, or stored procedures.

This method allows you to specify an XQuery or XPath expression to evaluate, and the result of this 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 that contains information about the pitcher of each group:
SELECT teamdoc.query ('/team/players/pitcher ')
From Team

This statement produces the following result:

----------------------------------------------


(1 row (s) affected)

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

exist method

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

exist (XQuery)

When you use this exist method, it calculates the XQuery query and returns a value of 1 if the query produces any results. For example, the following statement queries the Group table row for the presence of starter pitchers in the Teamdoc domain:

-The following is a simple exist statement:

SELECT Count (*)
From Team
WHERE Teamdoc.exist (
'/team/players/pitcher[@role = "Starter"]) = 1

Value method

This value method is helpful when you do not want to explain the results of the entire query and only want a scalar value. This value method is used to query the XML and return an atomic value. The syntax for this value method is as follows:

Value (Xquery,datatype)

With the value method, you can get a single scalar value from the XML. To do this, you must specify the XQuery statement and the data type that you want it to return, and you can return any data type except for the XML data type. For example, if you want to get the name of the first pitcher in each group, you can write the following form of query:

--Make a query to get a single value

SELECT Teamdoc.value (
' (/team/players/pitcher/@name) [1] ',
' nvarchar (max) ')
As Firstpitcher
From Team

The result of this query in the scalar value of the first pitcher in each of the teams is the following return value:
Firstpitcher
------------------------------
John Smoltz
(1 row (s) affected)

Note that the difference between the query and the value methods is that the Query method returns an XML data type-it contains the result of the query, and the value method returns a non-XML data type with the result of the query. In addition, the value method can only return a single value (or a scalar value). If you attempt to create an XQuery expression that returns more than one value using the value method, you will get an error.

Modify method

Although the XQuery standard does not provide a mechanism to update XML, SQL Server 2005 provides a way to modify the part of an XML object in a timely manner. This means that you don't have to retrieve a complete XML document just for modification. In order to modify a document on the fly, you can use a combination of the-modify method and the new XML Data modification language (XML DML) in SQL Server 2005.

The syntax for the Modify method is:

Modify ( )

The method uses only one parameter: an XML DML statement. XML DML is also similar to SQL's insert,update and delete syntax, but not the same. For example, you can modify the XML by using the Insert DML statement:

SET @doc. Modify ('
Insert as Last
Into (/team/players) [1]
‘)

Alternatively, you can do the same thing by calling an UPDATE statement and modifying an XML column:

--Modify an XML document without completely replacing it:

UPDATE Team
SET teamdoc.modify ('
Insert as Last
Into (/team/players) [1]
‘)
WHERE teamdoc.exist ('/team[@name = "Braves"] ') = 1

Note that the SET clause in this UPDATE statement does not follow the set x = y pattern that you used to write SQL in the past. The syntax assumes that you can provide a completely new value instead of the old one-which in the case of XML means that you want to use a completely new document instead of the old document. When working with XML types, the Modify method can instantly modify the original document. That is, for SQL Server, you do not have to try to replace the entire document with every modification. The set syntax in this example reflects a more efficient way to modify a document on the fly.

There are three types of XML DML statements: Insert,update and delete. These three statements are used to insert, update, and delete portions of an XML object, respectively. The syntax for each method is similar to SQL, but there are some obvious differences.

The following syntax is appropriate for the INSERT statement:

Insert
Insertexpression (
{as First | as last}
into | After | Before} locationexpression
)

The INSERT statement is followed by the XML (insertexpression) you want to insert. Next, you need to specify how you want to insert the XML. Your choice is into,after or before. Where the before and after clause directives database inserts insertexpression as a sibling of locationexpression (sibling). Before or after specifies whether to insert it before or after the locationexpression:

SET @doc. Modify ('
Insert Name= "Jaret Wright"/>
Before (/team/players/pitcher) [1]
‘)

This into clause inserts insertexpression as a child node of locationexpression. Optional clauses as first and as last are used to specify where to insert the child's node:

--Insert within the group

SET @doc. Modify ('
Insert Name= "Jaret Wright"/>
Into (/team/players) [1]
‘)

--Insert within the group, specifying that it should

--Insert as last element

SET @doc. Modify ('
Insert Name= "Jaret Wright"/>
As last into (/team/players) [1]
‘)

The syntax of the DELETE statement is straightforward:

Delete Locationexpression

This locationexpression specifies what content to remove from the XML data. For example, to delete all the pitcher:

SET @doc. Modify (' Delete/team/player/pitcher ')

Because the query specifies all the pitcher elements, they are all deleted. If you want to delete only one element, you can specify the identity attribute. For example, to delete only the pitcher John Smoltz, you can write the following delete statement:

SET @doc. Modify ('
delete/team/players/pitcher[@name = "John Smoltz"]
‘)

You can have the DELETE statement delete a single property. For example, in order to remove the role attribute for the pitcher John Smoltz, the corresponding XML DML looks like this:

SET @doc. Modify ('
delete/team/players/pitcher[
@name = "John Smoltz"]/@role ')

Finally, the Replace value statement describes the modification of the XML data. The syntax for this replace value statement is as follows:

Replace value of
Originalexpression
With
Replacementvalue | If

This replace value statement is used to modify the values in the XML. The only possible value is the content of a label or the value of an attribute. This originalexpression must be parsed into a single node or attribute. This replacementvalue is usually a value to replace. Instead of a node, the content requires that you use an XQuery expression of the text () function to specify the text that you want to replace a node. For example, to replace the inner text of a pitcher (inner text), you could write a modify statement similar to the following:

DECLARE @doc XML
SELECT @doc = '



With Team since 1989


'
SET @doc. Modify ('
Replace value of (/team/players/pitcher[
@name = "John Smoltz"]/text ()) [1]
With "could start in 2005"
‘)

Modifying a property is straightforward: You only need to use an XQuery expression to parse a single property. For example, in order to replace the value of the role attribute of the pitcher John Smoltz with "Starter", you can write the following statement:

SET @doc. Modify ('
Replace value of (/team/players/pitcher[
@name = "John Smoltz"]/@role) [1]
With "Starter"
‘)

The Replace value syntax also supports conditional substitution, which can be achieved by using the IF...THEN...ELSE syntax within the WITH clause of the REPLACE value statement. For example, if John Smoltz is a closer, replace his role with starter, but if he is not a starter, change the role property to closer; then you can write the following code:

SET @doc. Modify ('
Replace value of (/team/players/pitcher[
@name = "John Smoltz"]/@role) [1]
With (
if (/team/players/pitcher[
@name = "John Smoltz"]/@role = "Closer") Then
"Starter"
Else
"Closer"
)
‘)

Nodes method

The nodes method is used to convert a set of nodes returned by a query into a set of record rows in a table similar to the result set. The syntax for this method is as follows:

Nodes (XQuery) Table (Column)

Here, XQuery is the expression that selects the node to be exposed as a result set. Table and column are used to specify names in the result set. Note that you can manipulate only one column and its automatic type is XML. For example, to query and get information for each pitcher, you can write the following code:

DECLARE @doc XML
SELECT @doc = '



With Team since 1989


'
SELECT Team.player.query ('. ') as pitcher
From @doc. Nodes ('/team/players/pitcher ')
Team (player)

These results in a single result set contain row data corresponding to each of the pitcher's elements:

Pitcher
--------------------------------------------


(2 row (s) affected)

Note that you use the Query method to return these nodes to the results. The reason for this is that the result of a nodes method may only be referenced by the XML method (query, modify, delete, and update) or is null and is a NOT NULL statement.

In general, you can use the nodes method to decompose XML into a more useful set of results. For example, you can use the nodes method to get the nodes of an athlete and then use the value method to retrieve them to get a single value as a scalar data:

SELECT Team.player.value (
'./@name ', ' nvarchar (ten) ' as name,
Team.player.value ('./@role ', ' nvarchar (ten) ') as
Playerrole
From @doc. Nodes ('/team/players/pitcher ')
Team (player)

This produces the following result:

Name Playerrole
--------------- ---------------
John Smoltz Closer
Russ Ortiz Starter
(2 row (s) affected)