SQL Server 2008 Books Online (October 2009)
xml Data Type Methods

You can use the xml data type methods to query an XML instance stored in a variable or column of xml type. The topics in this section describe how to use the xml data type methods.

In This Section

Topic Description

query() Method (xml Data Type)

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

value() Method (xml Data Type)

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

exist() Method (xml Data Type)

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

modify() Method (xml Data Type)

Describes how to use the modify() method to specify XML Data Modification Language (XML DML)statements to perform updates.

nodes() Method (xml Data Type)

Describes how to use the nodes() method to shred XML into multiple rows, which propagates parts of XML documents into rowsets.

Binding Relational Data Inside XML Data

Describes how to bind non-XML data inside XML.

Guidelines for Using xml Data Type Methods

Describes guidelines for using the xml data type methods.

You call these methods by using the user-defined type method invocation syntax. For example:

SELECT XmlCol.query(' ... ')
FROM   Table
ms190798.note(en-us,SQL.100).gifNote:
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.
See Also

Other Resources

Typed XML Compared to Untyped XML
Generating XML Instances
Implementing XML in SQL Server
Sample XML Applications

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Community Content

skim milk
Cannot pass a variable to XML Data Methods for XQuery parameter

Following methods accept a string literal for XQuery parameter

  • query() Method (xml Data Type)
  • value() Method (xml Data Type)

So you cannot pass a variable to those methods for XQuery parameter.
That also means you cannot create UDFs or Stored Procedures to pass XQuery expession as following example shows.

declare @XQVarchar varchar(100), @XQNvarchar nvarchar(100)

select

@XQVarchar ='(/root/node)[1]', @XQNvarchar =N'(/root/node)[1]'

;

WITH q AS (SELECTCAST('<root><node>1</node></root>'ASXML)AS doc)

SELECT

doc.query(@XQVarchar), doc.query(@XQNvarchar),

doc

.value(@XQVarchar,'INT'), doc.query(@XQNvarchar,'INT')

FROM

q

Above statement will generate following error

Msg 8172, Level 16, State 1, Line 3

The argument 1 of the xml data type method "query" must be a string literal.


Tags :

Thomas Lee
SQL XML Tips and Tricks

Refer to my blog for helpful advice on SQL XML and SQL DML examples and related performance issues.

Jeff Fischer


mrshrinkray
SQL Variables

You can use SQL variables like so:

UPDATE mytable SET Xmldata.modify('replace value of (/Root/@Name)[1] with (sql:variable("@myVar"))') WHERE id='@myId'



Chris

Tags : sql:variable

Page view tracker