Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

FOR XML Support for the xml Data Type

If a FOR XML query specifies a column of xml type in the SELECT clause, column values are mapped as elements in the returned XML, regardless of whether you specify the ELEMENTS directive. Any XML declaration in the xml type column is not serialized.

For example, the following query retrieves customer contact information such as the ContactID, FirstName, and LastName columns, and the telephone numbers from the AdditionalContactInfo column of xml type.

SELECT ContactID, FirstName, LastName, AdditionalContactInfo.query('
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
 //act:telephoneNumber/act:number
') AS PhoneNumber
FROM Person.Contact
FOR XML AUTO, TYPE

Because the query does not specify the ELEMENTS directive, the column values are returned as attributes, except for the additional contact information values retrieved from the xml type column. These are returned as elements.

This is the partial result:

<Contact ContactID="1" FirstName="Syed" LastName="Abbas">
    <act:number xmlns:act=
       "http://schemas.adventure-works.com/AdditionalContactTypes">
          111-111-1111</act:number>
    <act:number xmlns:act=
       "http://schemas.adventure-works.com/AdditionalContactTypes">
         112-111-1111</act:number>
</Contact>
<Contact ContactID="2" FirstName="Catherine" LastName="Abel">
    ...
</Contact>
...

If you specify a column alias for the XML column generated by the XQuery, that alias is used to add a wrapper element around the XML generated by the XQuery. For example, the following query specifies MorePhoneNumbers as a column alias:

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

The XML returned by the XQuery is wrapped in the <MorePhoneNumbers> element, as shown in the following partial result:

<Contact ContactID="1" FirstName="Syed" LastName="Abbas">
  <MorePhoneNumbers>
    <act:number xmlns:act="http://schemas.adventure-works.com/AdditionalContactTypes">111-111-1111</act:number>
    <act:number xmlns:act="http://schemas.adventure-works.com/AdditionalContactTypes">112-111-1111</act:number>
  </MorePhoneNumbers>
</Contact>
<Contact ContactID="2" FirstName="Catherine" LastName="Abel">
  <MorePhoneNumbers>
        ...
  </MorePhoneNumbers>
</Contact>
...

If you specify the ELEMENTS directive in the query, the ContactID, LastName, and FirstName will be returned as elements in the resulting XML.

The following example illustrates that the FOR XML processing logic does not serialize any XML declarations in the XML data from an xml type column:

create table t(i int, x xml)
go
insert into t values(1, '<?xml version="1.0" encoding="UTF-8" ?>
                             <Root SomeID="10" />')
select i, x
from   t
for xml auto

This is the result. In the result, the XML declaration <?xml version="1.0" encoding="UTF-8" ?> is not serialized.

<root>
  <t i="1">
    <x>
      <Root SomeID="10" />
    </x>
  </t>
</root>

FOR XML queries can be used for returning XML from a user-defined function that returns either of the following:

  • A table with a single xml type column

  • An instance of the xml type

For example, the following user-defined function returns a table with a single column of xml type:

CREATE FUNCTION MyUDF (@ProudctModelID int)
RETURNS @T TABLE
  (
     ProductDescription xml
  )
AS
BEGIN
  INSERT @T
     SELECT CatalogDescription.query('
declare namespace PD="http://www.adventure-works.com/schemas/products/description";
                    //PD:ProductDescription  ')
     FROM Production.ProductModel
     WHERE ProductModelID = @ProudctModelID
  RETURN
END

You can execute the user-defined function and query the table returned by it. In this example, the XML returned by querying the table is assigned to an xml type variable.

declare @x xml
set @x = (SELECT * FROM MyUDF(19))
select @x

This is another example of a user-defined function. This user-defined function returns an instance of the xml type. In this example, the user-defined function returns a typed XML instance, because the schema namespace is specified.

drop function MyUDF4
go
CREATE FUNCTION MyUDF4 (@ProductModelID int) 
RETURNS xml ([Production].[ProductDescriptionSchemaCollection])
AS
BEGIN
  declare @x xml
  set @x =   ( SELECT CatalogDescription
          FROM Production.ProductModel
          WHERE ProductModelID = @ProductModelID )
  return @x
END

The XML returned by the user-defined function can be assigned to an xml type variable as follows:

declare @x xml
SELECT @x= dbo.MyUDF4 (19) 
select @x
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.