Microsoft® SQL Server® 2012 Bible

355

Chapter 14: Using XML Data

14

The question from the preceding example is why CROSS APPLY is used. When working with

XML columns, multiple XML documents are processed in a single batch. The CROSS APPLY

operator helps with this processing.

exist()

The exist()method checks for the existence of a specifi ed element or attribute specifi ed

in the XPath expression. The following example uses the exist() method to fi lter rows

that have a specifi c item number:

SELECT OrderID

FROM ItemInfo

WHERE ItemData.exist('Order/Item/ItemNumber = "A017"') = 1

The exist() method returns true (1) if an element or attribute exists in the XML

document that is specifi ed in the XPath expression.

query() and modify()

The query() method takes an XQuery expression and evaluates it to a list of XML elements

that can be accessed and processed further. The modify() method is used to modify XML

documents, either insert, update, and delete operations on elements or attributes.

These methods are discussed in more detail when discussing the FLWOR operations and data

modifi cation later in the chapter.

FOR XML

Up until now you have talked about how to query XML, either a document or fragment, and

return tabular data. The FOR XML clause does the reverse, meaning it takes tabular data

and returns XML.

The FOR XML clause is a rowset aggregation function that returns a one-row, one-column

result set containing an NVARCHAR(MAX) value. Several directives can be applied to the

FOR XML clause, which provides different control and structure over the resulting XML.

This section discusses the FOR XML clause and the different directives that can be applied.

Before discussing the modes, you need to understand that the XMLDATA directive to the FOR XML options has been

deprecated in SQL Server 2012. In its place you should use XSD generation when using the RAW and AUTO modes.

Auto

The AUTO directive is the easiest of the directives to use in which to generate XML output

from results specifi ed in the SELECT statement. Although it is certainly the easiest of the

c14.indd 355c14.indd 355 7/30/2012 4:49:02 PM7/30/2012 4:49:02 PM

http://www.it-ebooks.info