You must use one of the five basic methods that available to the XML data type —

• query ('XQuery')

  Specifies an XQuery against an instance of the xml data type. The result is of xml type. The method returns an instance of untyped XML.

• value (XQuery, SQLType)

  Performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value.

  You typically use this method to extract a value from an XML instance stored in an xml type column, parameter, or variable. In this way, you can specify SELECT queries that combine or compare XML data with data in non-XML columns.

• exist (XQuery)

  Returns a bit that represents one of the following conditions:

        1, representing True, if the XQuery expression in a query returns a nonempty result. That is, it returns at least one XML node.
        0, representing False, if it returns an empty result.
        NULL if the xml data type instance against which the query was executed contains NULL.
  

• nodes (XQuery) as Table(Column)

  The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.

  Every xml data type instance has an implicitly provided context node. For the XML instance stored in a column or variable, this is the document node. The document node is the implicit node at the top of every xml data type instance.

  The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes. You can retrieve multiple values from the rowset.

  For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. Note that the value() method, when applied to the XML instance, returns only one value.

• modify (XML_DML)

  Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or

  delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

There are three data retrieval XML methods available in SQL Server 2005 i.e query, nodes and value

Which one to use and when? and few XML Optimization Tips

• The xml data type methods query(), value(), and exist() return NULL if executed against a NULL XML instance. Also, modify() does not return anything, but nodes() returns rowsets and an empty rowset with a NULL input.

• Using the value() and exist() methods to retrieve values from an xml type column

The following example shows using both the value() method and the exist() method of the xml data type.

The value() method is used to retrieve ProductModelID attribute values from the XML. The exist() method in the WHERE clause is used to filter the rows from the table.

Example

SELECT CatalogDescription.value('
     declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
     (/PD:ProductDescription/@ProductModelID)[1] ', 'int') as Result
FROM  Production.ProductModel
WHERE CatalogDescription.exist('
declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";    
/PD:ProductDescription/PD:Features/wm:Warranty ') = 1

• For performance reasons, instead of using the value() method in a predicate to compare with a relational value, use exist() with sql:column(). For example:

  CREATE TABLE T
  

  (c1 int, c2 varchar(10), c3 xml) GO
  SELECT c1, c2, c3 FROM T WHERE c3.value( '/root[1]/@a', 'integer') = c1 GO

This can be written in the following way:

 SELECT c1, c2, c3 
    FROM T
    WHERE c3.exist( '/root[@a=sql:column("c1")]') = 1
    GO

Note that the rowset returned has maintained the type information. You can apply xml data type methods, such as query(), value(), exist(), and nodes(), to the result of a nodes() method. However, you cannot apply the modify() method to modify the XML instance.

Also, the context node in the rowset cannot be materialized. That is, you cannot use it in a SELECT statement. However, you can use it in IS NULL and COUNT(*). 

Note that the rowset returned by the nodes() method is an unnamed rowset.

• The nodes() function cannot be applied directly to the results of a user-defined function. To use the nodes() function with the result of a scalar user-defined function, you can either assign the result of the user-defined function to a variable or use a derived table to assign a column alias to the user-defined function return value and then use CROSS APPLY to select from the alias.

Example

USE AdventureWorks2008R2;
GO

CREATE FUNCTION XTest()
RETURNS xml
AS
BEGIN
RETURN '<document/>';
END;
GO


SELECT A2.B.query('.')
FROM
(SELECT dbo.XTest()) AS A1(X) 
CROSS APPLY X.nodes('.') A2(B);
GO

DROP FUNCTION XTest;
GO

• If values are needed rather than XML nodes then the value() function should be used to access the data (obvious, isn't it?). When designing queries it is vital to take into account that the XQuery results evaluate to singletons before the value() function can be applied.

• Use the XQuery value() method instead of the query() method when you want to return event data. Using the XQuery value() method instead of the query() method may be more efficient because the query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the value() method renders CR/LF instances invisible in the output.

• Use SQLXML instead of Native XML Web Services for applications characterized by real time highly concurrent access, with short duration transactions. In this case, using SQLXML instead of Native XML Web Services can be much more efficient and can provide best performance.

• The xml data type methods are treated internally as subqueries. Because GROUP BY requires a scalar and does not allow aggregates and subqueries, you cannot specify the xml data type methods in the GROUP BY clause. A solution is to call a user-defined function that uses XML methods inside of it.

• Consider creating indexes on XML columns. SQL Server 2008 supports indexes on XML columns. XML data stored in XML data type columns as large binary objects, which size can be up to 2 GB. Without an index, the XML instances are shredded at run time to evaluate a query. Using index on a XML column allows the Database Engine to more efficiently process the documents and fragments.

• Use XQuery for querying XML data stored using the XML data type. SQL Server 2008 supports XQuery language that is based on working drafts of the World Wide Web Consortium (W3C) XQuery language definitions. This query language is used for querying XML data stored using the XML data type.

• Consider using XML format files to bulk import data into tables or non-partitioned views and to bulk export data. SQL Server 2008 supports new type of the format files. There is XML format file. XML format files are more flexible and powerful than non-XML format files and easy to understand and create. So, consider using XML format files instead of non-XML format files.

• Use XML Data Manipulation Language (XML DML) if you need to insert, update, and delete XML documents or fragments. In comparison with the current W3C definition of XQuery that does not include the ability to modify XML documents, SQL Server 2008 has extended the XQuery implementation to include the ability to insert, update, and delete XML documents or fragments. So, if you need to modify XML documents, you can use XML DML in SQL Server 2008.

• The xml data type methods cannot be used in the PRINT statement as shown in the following example. The xml data type methods are treated as subqueries, and subqueries are not allowed in the PRINT statement. As a result, the following example returns an error:

  DECLARE @x xml SET @x = 'Hello' PRINT @x.value('/root[1]', 'varchar(20)') /* will not work because this is treated as a subquery (select top 1 col from table) */

  -- Below Workable Code DECLARE @c varchar(max) SET @c = @x.value('/root[1]', 'varchar(11)') PRINT @c
  GO

Limitations of the XML Data Type

Although the XML datatype is treated like many other datatypes in SQL Server 2005, there are specific limitations to how it is used. These limitations are:

• XML types cannot convert to text or ntext data types.

• No data type other than one of the string types can be cast to XML.

• XML columns cannot be used in GROUP BY statements.

• Distributed partitioned views or materialized views cannot contain XML data types.

• Use of the sql_variant instances cannot include XML as a subtype.

• XML columns cannot be part of a primary or foreign key.

• XML columns cannot be designated as unique.

• Collation (COLLATE clause) cannot be used on XML columns.

• XML columns cannot participate in rules.

• The only built-in scalar functions that apply to XML columns are ISNULL and COALESCE. No other scalar built-in functions are supported for use against XML types. Tables can have only 32 XML columns.

• Tables with XML columns cannot have a primary key with more than 15 columns.

• Tables with XML columns cannot have a timestamp data type as part of their primary key.

• Only 128 levels of hierarchy are supported within XML stored in the database.

Reference http://msdn.microsoft.com/en-us/library/ms190798.aspx

http://www.sqlservergeeks.com/articles/sql-server-bi/50/sql-server-xml-data-type-methods-part-1

Thanks

Yogesh

What is XQuery?
XQuery is a new language for querying XML data. XQuery is a lot more interactive. It allows complex queries against the XML (using FLWOR), as well as interaction with the database so that external variables and SQL columns can be used for value matching etc. It also allows writes back to the source XML, and in the case of an XML column in a table, can be applied across all rows in a single statement .We can use XQUERY with the CROSS APPLY join to process database XML data on a row by row basis.

The five methods that available to the XML data type are as below

1. query()
2. value()
3. exist()
4. nodes()
5. modify()

1. query()
Syntax
query ('XQuery')

SELECT @x.query('/christmaslist/person')

The query() method retrieves a subset of untyped XML from the target XML instance. It’s probably the simplest and most straightforward of the XML methods. You need only specify the database object, the method name, and an XQuery expression.

2. value()
Syntax
value (XQuery, SQLType)

SELECT @x.value('/christmaslist[1]/person[1]/@name', 'VARCHAR(20)')

The value() method returns a scalar value from the targeted XML document. The returned value is converted to the data type you specify when you call the method. The value() method makes it easier to work with individual values in order to compare or combine them, either with other XML values or values of different types.SQLType cannot be an xml data type, a common language runtime (CLR) user-defined type, image, text, ntext, or sql_variant data type. SQLType can be an SQL, user-defined data type.

3. exist()
Syntax
exist (XQuery)

SELECT @x.exist('/christmaslist/person[@gift = "socks"]')

The key to using the exist() method is in understanding the values it returns. Unlike the query() and value() methods, the exist() method doesn’t return XML content. Rather, the method returns one of the following three values:

• A BIT value of 1 if the XQuery expression returns a nonempty result
• A BIT value of 0 if the XQuery expression returns an empty result.
• A NULL value if the XML data type instance is null.

4. nodes()
Syntax
nodes (XQuery) as Table(Column)

SELECT
      Table1.Column1.value('@name',VARCHAR(20)')
FROM   @x.nodes('/christmaslist/person') as Table1(Column1)93

The nodes() method can be a bit more slippery to understand than the other XML methods. To begin with, rather than returning XML or scalar values, the nodes() method returns what is essentially a table that includes one column. That means you should use the method only in those parts of a statement that can handle rowset views, such as the FROM clause. It also means that, when you call the nodes() method, you must assign a table alias and column alias to the rowset view returned by the method.Shreds XML data to relational data, identifying nodes-to-row mapping.

5. modify()
Syntax
modify (XML_DML)

   SET x.modify(' replace value of (/christmaslist/persont/@name)[1]  with "Mitesh"

To change or remove a portion of the content in an XML variable or column, the modify() method can be used. XML Data Manipulation Language (DML) is used to specify the action within the modify() method. With XML DML you can insert nodes, change nodes, and delete nodes.The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

The XML DML adds the following case-sensitive keywords to XQuery:

• insert
• delete
• replace value of

Example with Explaination

http://www.simple-talk.com/sql/learn-sql-server/the-xml-methods-in-sql-server/

http://www.techrepublic.com/article/shred-xml-data-with-xquery-in-sql-server-2005/6140404#

XQuery Labs - A Collection of XQuery Sample Scripts

http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

When would you use one over the other?

• If values are needed rather than XML nodes then the value function should be used to access the data
• If XML nodes are needed rather than values then the node() function should be used to access the data
• If you want to check the existence of data exist() method is use.
• The query() method is used to specify a query against an XML instance that is stored in an xml data type variable or column. Sometimes, you might also want your query to use values from a Transact-SQL variable, or parameter, to bring relational and XML data together. To do this, you use the sql:variable function.
• Three data retrieval XML methods available are query(),nodes(),value()
• When you want to modify (insert,delete,update) xml data than modify() method is useful.
• When you want to return event data, use the XQuery value() method instead of the query() method. The query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the value() method renders CR/LF instances invisible in the output.

5 methods are query() Method,value() Method,exist() Method,modify() Method,nodes() Method.

If values are needed rather than XML nodes then the value function should be used to access the data. If XML nodes are needed rather than values then the node() function should be used to access the data. If you want to check the existence of data exist() method is use. The query() method is used to specify a query against an XML instance that is stored in an xml data type variable or column. Sometimes, you might also want your query to use values from a Transact-SQL variable, or parameter, to bring relational and XML data together. To do this, you use the sql:variable function. Three data retrieval XML methods available are query(),nodes(),value() When you want to modify (insert,delete,update) xml data than modify() method is useful. When you want to return event data, use the XQuery value() method instead of the query() method. The query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the value() method renders CR/LF instances invisible in the output

You can use the xml data type methods to query an XML instance stored in a variable or column of xml type.

Following are the methods;

1. query()
2. value()
3. exists()
4. modify()
5. nodes()

query()

Describes how to use the query() method to query over an XML instance.

declare @myDoc xml
set @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
  <Warranty>1 year parts and labor</Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
SELECT @myDoc.query('/Root/ProductDescription/Features')

The query retrieves the child element of the element.

value()

Describes how to use the value() method to retrieve a value of SQL type from an XML instance

In the following example, an XML instance is stored in a variable of xml type. The value() method retrieves the ProductID attribute value from the XML. The value is then assigned to an int variable.

DECLARE @myDoc xml
DECLARE @ProdID int
SET @myDoc = '<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
  <Warranty>1 year parts and labor</Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'

SET @ProdID =  @myDoc.value('(/Root/ProductDescription/@ProductID)[1]', 'int' )
SELECT @ProdID

exist()

Describes how to use the exist() method to determine whether a query returns a nonempty result.

In the following example, @x is an xml type variable (untyped xml) and @f is an integer type variable that stores the value returned by the exist() method. The exist() method returns True (1) if the date value stored in the XML instance is 2002-01-01.

declare @x xml
declare @f bit
set @x = '<root Somedate = "2002-01-01Z"/>'
set @f = @x.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2002-01-01Z")]')
select @f

modify()

Describes how to use the modify() method to specify XML Data Modification Language (XML DML)statements to perform updates. In the following example, a document instance is first assigned to a variable of xml type. Then, replace value of XML DML statements update values in the document.

DECLARE @myDoc xml
SET @myDoc = '<Root>
<Location LocationID="10" 
            LaborHours="1.1"
            MachineHours=".2" >Manufacturing steps are described here.
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>'
SELECT @myDoc

-- update text in the first manufacturing step
SET @myDoc.modify('
  replace value of (/Root/Location/step[1]/text())[1]
  with     "new text describing the manu step"
')
SELECT @myDoc
-- update attribute value
SET @myDoc.modify('
  replace value of (/Root/Location/@LaborHours)[1]
  with     "100.0"
')
SELECT @myDoc

nodes()

Describes how to use the nodes() method to shred XML into multiple rows, which propagates parts of XML documents into rowsets. The query then returns the context node from each row:

DECLARE @x xml ;
SET @x='<Root>
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>
    <row id="2"><name>moe</name></row>
    <row id="3" />
</Root>';
SELECT T.c.query('.') AS result
FROM   @x.nodes('/Root/row') T(c);
GO

The XML data type (specified as XML) is a built-in data type just like varchar, int, and others. You use it the way you'd use any other SQL Server data type. Columns, variables, parameters, and values that functions return can all use the XML data type. A user can create a table that has one or more columns of type XML in addition to relational columns.

Typed or Untyped XML

Like other datatypes, the XML datatype must meet specific formatting criteria. When the XML data is associated with an XML schema collection, it is called "typed XML"; otherwise it is called "untyped XML". Both typed and untyped XML are accommodated within a single framework, the XML data model is preserved, and query processing enforces XML semantics.

Example: Untyped XML

CREATE TABLE tblXML (ID INT PRIMARY KEY, xmlVal XML not null)   

Example: Typed XML XML columns, variables, and parameters can be bound to a collection of XML schemas. The following statement creates schema named mySchema,

CREATE XML SCHEMA COLLECTION mySchema AS 'mySchema'    
                                                   

The following statement creates a table named tblXMLCatalog with an XML column Document typed using mySchema. The typed XML column is also specified to accept XML fragments, not just XML documents.

CREATE TABLE tblXMLCatalog (ID INT PRIMARY KEY, Document XML(CONTENT mySchema)) INSERT tblXMLCatalog VALUES(2,'1212Product1')  

Methods on XML Data Type:

You can retrieve entire XML values or you can retrieve parts of XML instances. This is possible by using four XML data type methods ,

1. query()
2. value()
3. exist()
4. nodes()
5. modify()

QUERY

This method is useful for extracting parts of an XML instance. The XQuery expression evaluates to a list of XML nodes. The subtree rooted at each of these nodes is returned in document order. The result type is untyped XML. Example:

INSERT INTO tblXML VALUES (1, '152Product1
212
Product1233') INSERT INTO tblXML VALUES (2, 'Item12Item23') INSERT INTO tblXML VALUES (2, 'Item12
Item23')   


SELECT ID, xmlVal.query('/Products//product//id') as XMLColumn FROM tblXML   

VALUE

This method extracts a scalar value from an XML instance. It returns the value of the node the XQuery expression evaluates to. This value is converted to a Transact-SQL type specified as the second argument of the value() method. Example:

SELECT xmlVal.value('data((/Items//Item/name)[1])', 'varchar(100)') as XMLColumn FROM tblXML   

MODIFY

This method permits modifying parts of an XML instance, such as adding or deleting subtrees, or replacing scalar values. This method allows modification of XML data and accepts an XML data modification statement as input. Example:

UPDATE tblXML SET xmlVal.modify('
insert

ItemNew
23
after (/Items//Item[@No = 111])[1]')   

Managing XML indexes:

Query performance can be improved on XML columns by creating an xml index. XML indexes are created by using a new DDL statement on typed and untyped XML columns. This creates a B+tree for all XML instances in the column. The first index on an XML column is the primary XML index. Using it, three types of secondary XML indexes are supported on the XML column to speed up common classes of queries, as described in the following section.

Primary XML Index

XML columns are stored as binary large objects (BLOBs) in your database. Because of this, queries that search within an XML column can be slow. You can speed up these queries by creating a primary XML index on each XML column. If the base table is partitioned, the primary XML index is partitioned the same way; that is, using the same partitioning function and partitioning scheme. To create a primary XML index, execute the CREATE PRIMARY XML INDEX statement:

The following statement creates a table named tblXML with a primary key column named ID and an untyped XML column named xmlVal.

CREATE PRIMARY XML INDEX index_name ON table_name (xml_column_name)   

Secondary XML Indexes In addition to the primary XML index, each XML column can have up to three secondary XML indexes —PATH, PROPERTY, and VALUE—benefit path-based queries, custom property management scenarios, and value-based queries, respectively.

 The PATH index helps with queries that use XML path expressions. The most common case is the use of the exist() method on XML columns in the WHERE clause of a SELECT statement. The PATH index is used if queries have to be executed to retrieve data from the xml column by specifying the path and value.

 The PROPERTY index helps with queries that retrieve particular object properties from within an XML document. This index is used to retrieve node values from the xml column by specifying a path.

 The VALUE index helps with queries that search for values anywhere in the XML document. This index benefits queries where a node's value is known but its path is imprecisely specified in the query.

 The ALTER INDEX statement is used to alter the table index and rebuild it.

 The DROP INDEX can be used to drop an index.

When would you use one over the other?

1. If values are needed rather than XML nodes then the value function should be used to access the data
2. If XML nodes are needed rather than values then the node() function should be used to access the data
3. If you want to check the existence of data exist() method is use.
4. The query() method is used to specify a query against an XML instance that is stored in an xml data type variable or column. Sometimes, you might also want your query to use values from a Transact-SQL variable, or parameter, to bring relational and XML data together. To do this, you use the sql:variable function.
5. Three data retrieval XML methods available are query(),nodes(),value()
6. When you want to modify (insert,delete,update) xml data than modify() method is useful.
7. When you want to return event data, use the XQuery value() method instead of the query() method. The query() method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the value() method renders CR/LF instances invisible in the output.

Reference: http://msdn.microsoft.com/en-us/library/ms190798.aspx

http://www.sqlservergeeks.com/articles/sql-server-bi/50/sql-server-xml-data-type-methods-part-1

Not Properly viewable in Internet Explorer browser hence added again the answer. but good visibility on Mozila firefox