Microsoft® SQL Server® 2012 Bible

363

Chapter 14: Using XML Data

14

Similar results can be achieved by using special column name indicators such as data()

and text(). For example, you can use the data() indicator to create a list of values sepa-

rated by spaces. They key here is to make the PATH name empty.

SELECT ItemNumber AS 'data()'

FROM Item

FOR XML PATH(''), ROOT('Items')

/*

<Items>V001 A017 P002</Items>

*/

The text() indicator can produce a similar string but without spaces (also using an empty

PATH name).

SELECT ItemNumber AS 'text()'

FROM Item

FOR XML PATH(''), ROOT('Items')

/*

<Items>V001A017P002</Items>

*/

Lastly, you can add comments to your XML by applying the comments() indicator; although

you still need to use the FOR XML PATH directive:

SELECT 'Order Number' AS 'comment()',

OrderNumber,

'CustomerID' AS 'comment()',

CustomerID

FROM Orders WHERE OrderID = 1

FOR XML PATH(''), ROOT

/*

<Items>V001A017P002</Items>

<root>

<!--Order Number-->

<OrderNumber>10001</OrderNumber>

<!--CustomerID-->

<CustomerID>1</CustomerID>

</root>

*/

XQuery and FLWOR Operations

XQuery is a language that provides the ability to query structured and semi-structured XML

data. XQuery is based on the existing XPath query language but includes added support for

improved iteration, sorting results, and XML construction.

c14.indd 363c14.indd 363 7/30/2012 4:49:03 PM7/30/2012 4:49:03 PM

http://www.it-ebooks.info