You must use one of the five
methods that available to the XML data type
1) query ('XQuery')
an XQuery against an instance of the xml data type. The result is of xml type.
returns an instance of untyped XML.
2) value (XQuery, SQLType)
an XQuery against the XML and returns a value of SQL type.
method returns a scalar value.
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.
3) exist (XQuery)
bit that represents one of the following conditions:
1, representing True, if the
XQuery expression in a query returns a nonempty result. That
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.
4) nodes (XQuery) as Table(Column)
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
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.
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.
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
5) modify (XML_DML)
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.
Which one to methode to use and
when? and few XML Optimization Tips
three data retrieval XML methods available in SQL Server 2005 i.e query, nodes
and value .
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.
', 'int') as Result
/PD:ProductDescription/PD:Features/wm:Warranty ') = 1
performance reasons, instead of using the value() method in a predicate to
compare with a relational value, use exist() with sql:column(). For example:
TABLE T (c1 int, c2 varchar(10), c3 xml)
c3.value( '/root/@a', 'integer') = c1
be written in the following way:
c3.exist( '/root[@a=sql:column("c1")]') = 1
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
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(*).
the rowset returned by the nodes() method is an unnamed rowset.
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.
dbo.XTest()) AS A1(X)
APPLY X.nodes('.') A2(B);
are needed rather than XML nodes then the value() function should be used to
access the data (obvious, isn't it?).
designing queries it is vital to take into account that the XQuery results
evaluate to singletons before the value() function can be applied.
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.
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.
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.
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.
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.
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
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.
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:
SET @x =
not work because this is treated as a subquery (select top 1 col from table)
SET @x =
SET @c =
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:
cannot convert to text or ntext data types.
type other than one of the string types can be cast to XML.
columns cannot be used in GROUP BY statements.
partitioned views or materialized views cannot contain XML data types.
Use of the
sql_variant instances cannot include XML as a subtype.
columns cannot be part of a primary or foreign key.
columns cannot be designated as unique.
(COLLATE clause) cannot be used on XML columns.
columns cannot participate in rules.
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.
have only 32 XML columns.
with XML columns cannot have a primary key with more than 15 columns.
with XML columns cannot have a timestamp data type as part of their primary
levels of hierarchy are supported within XML stored in the database.