TYPE Directive in FOR XML Queries

Artykuł
12/03/2008

In SQL Server 2000, the result of a FOR XML query is always directly returned to the client in textual form. With support for the xml data type in SQL Server 2005, you can optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive. This allows you to process the result of a FOR XML query on the server. For example, you can specify an XQuery against it, assign the result to an xml type variable, or write Nested FOR XML queries.

Note

SQL Server returns XML data type instance data to the client as a result of different server-constructs such as FOR XML queries that use the TYPE directive, or where the xml data type is used to return XML instance data values from SQL table columns and output parameters. In client application code, the ADO.NET provider requests this XML data type information to be sent in a binary encoding from the server. However, if you are using FOR XML without the TYPE directive, the XML data comes back as a string type. In any case, the client provider will always be able to handle either form of XML.

Examples

The following examples illustrate the use of FOR XML query.

A. Retrieving FOR XML query results as xml type

The following query retrieves customer contact information from the Contacts table. Because the TYPE directive is specified in FOR XML, the result is returned as xml type.

SELECT ContactID, FirstName, LastName, Phone
FROM Person.Contact
ORDER BY ContactID
FOR XML AUTO, TYPE

This is the partial result:

<Contact ContactID="1" FirstName="Syed" LastName="Abbas" 
         Phone="398-555-0132"/>
<Contact ContactID="2" FirstName="Catherine" LastName="Abel" 
         Phone="747-555-0171"/>
...

B. Assigning FOR XML query results to an xml type variable

In the following example, a FOR XML result is assigned to an xml type variable, @x. The query retrieves contact information, such as the ContactID, FirstName, LastName, and additional telephone numbers, from the AdditionalContactInfo column of xml TYPE. Because the FOR XML clause specifies TYPE directive, the XML is returned as xml type and is assigned to a variable.

DECLARE @x XML
SET @x = (
   SELECT ContactID, 
          FirstName, 
          LastName, 
          AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
              //act:telephoneNumber/act:number') as MorePhoneNumbers
   FROM Person.Contact
   FOR XML AUTO, TYPE)
SELECT @x
GO

C. Querying results of a FOR XML query

The FOR XML queries return XML. Therefore, you can apply xml type methods, such as query() and value(), to the XML result returned by FOR XML queries.

In the following query, the query() method of the xml data type is used to query the result of the FOR XML query. For more information, see query() Method (xml Data Type)).

SELECT (SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
 //act:telephoneNumber/act:number
') as PhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE).query('/Person.Contact[1]')

The inner SELECT … FOR XML query returns an xml type result to which the outer SELECT applies the query() method to the xml type. Note the TYPE directive specified.

This is the result:

<Person.Contact ContactID="1" FirstName="Gustavo" LastName="Achong">
  <PhoneNumbers>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">111-111-1111</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">112-111-1111</act:number>
  </PhoneNumbers>
</Person.Contact>

In the following query, the value() method of the xml data type is used to retrieve a value from the XML result returned by the SELECT…FOR XML query. For more information, see value() Method (xml Data Type)).

declare @FirstPhoneFromAdditionalContactInfo varchar(40);
SELECT @FirstPhoneFromAdditionalContactInfo = 
 ( SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace aci="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";

   //act:telephoneNumber/act:number
   ') as PhoneNumbers
   FROM Person.Contact Contact
   FOR XML AUTO, TYPE).value('
declare namespace act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
  /Contact[@ContactID="1"][1]/PhoneNumbers[1]/act:number[1]', 'varchar(40)'
 )
select @FirstPhoneFromAdditionalContactInfo

The XQuery path expression in the value() method retrieves the first telephone number of a customer contact whose ContactID is 1.

Note

If the TYPE directive is not specified, the FOR XML query result is returned as type nvarchar(max).

D. Using FOR XML query results in INSERT, UPDATE, and DELETE (Transact-SQL DML)

The following example demonstrates how FOR XML queries can be used in Data Manipulation Language (DML) statements. In the example, the FOR XML returns an instance of xml type. The INSERT statement inserts this XML into a table.

CREATE TABLE T1(intCol int, XmlCol xml)
go
INSERT INTO T1 
VALUES(1, '<Root><ProductDescription ProductModelID="1" /></Root>')
go

CREATE TABLE T2(XmlCol xml)
go
INSERT INTO T2(XmlCol) 
SELECT (SELECT XmlCol.query('/Root') 
        FROM T1 
        FOR XML AUTO,TYPE) 
go

Udostępnij za pośrednictwem