XQuery

The XML data type introduced in Chapter 10, is supported with a method to retrieve all or part of an XML document. The is made possible by the support of T-SQL for a subset of the XQuery language specifically to query the xml data type.

XQuery is a new adventure in XML that is based on the existing XPath query language. It has better support for iteration, sorting, and the construction of the XML. XQuery operates on the XQuery data model. To query an XML instance stored in a variable or column of xml type, you use the xml Data Type Methods. For example, you can declare a variable of xml type and query it by using the query() method of the xml data type. Have a look at the following code:

 DECLARE @X XML set @X = '<MyTable><Field1>One</Field1></MyTable>' select @X.query ('/MyTable')

produces

 <MyTable>   <Field1>One</Field1> </MyTable>

or

 DECLARE @X XML set @X = '<MyTable><Field1>One</Field1></MyTable>' select @X.query ('/MyTable/Field1')

produces

 <Field1>One</Field1> 

and

 DECLARE @X XML set @X = '<MyTable><Field1>One</Field1></MyTable>' select @X.query('/MyTable/Field1 [1]')

produces

 <Field1>One</Field1>

The method is also useful when working with ADO.NET datasets. For example the following dataset XML can be assigned to the variable as follows:

 DECLARE @X XML set @X = '<NewDataSet>   <Table>     <ClientID>12145</ClientID>     <ContactID>124201</ContactID>     <DropDate>2006–05–02T02:00:00–04:00</DropDate>     <ResponseDate>2006–05–22T11:02:34.837–04:00</ResponseDate>     <ResponseTypeID>1</ResponseTypeID>     <FullName>Ms Janet Roach</FullName>     <FirstName>Janet</FirstName>     <LastName>Roach</LastName>     <Address>Under the sink</Address>     <City>Filthy Lake</City>     <State>FL</State>     <ZipCode>Y21962</ZipCode>     <Phone>555XXX2222</Phone>     <LocationID>25</LocationID>     <LeadSourceID>0</LeadSourceID>     <SourceCode>0506_flor_a</SourceCode>     <DoNotCall>false</DoNotCall>     <DoNotMail>false</DoNotMail>     <DoNotContact>false</DoNotContact>     <Deceased>false</Deceased>     <Email1>[e-mail]</Email1>     <WebCCComments>stopbotheringme</WebCCComments>   </Table> </NewDataSet>'

And it can be queried thus:

 select @X.query ('/NewDataSet/Table/DoNotMail')

to return

 <DoNotMail>false</DoNotMail>

XQuery is now the staple query language for all things XML and SQL Server as we will see in the next sections.