Edit

Share via

FOR XML support for the XML data type

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

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 isn't serialized.

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

USE AdventureWorks2022;
GO

SELECT BusinessEntityID,
    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.Person
WHERE AdditionalContactInfo.query('
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number
') IS NOT NULL
FOR XML AUTO, TYPE;

Because the query doesn't specify the ELEMENTS directive, the column values are returned as attributes, except for the extra contact information values retrieved from the xml type column. These are returned as elements.

This is the partial result:

<Person.Person BusinessEntityID="291" FirstName="Gustavo" LastName="Achong">
  <PhoneNumber>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1112</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1111</act:number>
  </PhoneNumber>
</Person.Person>
<Person.Person BusinessEntityID="293" FirstName="Catherine" LastName="Abel">
  <PhoneNumber>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-2222</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-1234</act:number>
  </PhoneNumber>
</Person.Person>
...

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 BusinessEntityID,
    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.Person
WHERE AdditionalContactInfo.query('
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number
') IS NOT NULL
FOR XML AUTO, TYPE;

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

<Person.Person BusinessEntityID="291" FirstName="Gustavo" LastName="Achong">
  <MorePhoneNumbers>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1112</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">425-555-1111</act:number>
  </MorePhoneNumbers>
</Person.Person>
<Person.Person BusinessEntityID="293" FirstName="Catherine" LastName="Abel">
  <MorePhoneNumbers>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-2222</act:number>
    <act:number xmlns:act="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">206-555-1234</act:number>
  </MorePhoneNumbers>
</Person.Person>
...

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

The following example illustrates that the FOR XML processing logic doesn't 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;

Here's the result set. In the result, the XML declaration <?xml version="1.0" encoding="UTF-8" ?> isn't serialized.

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

Return XML from a user-defined function

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:

USE AdventureWorks2022;
GO

CREATE FUNCTION dbo.MyUDF (@ProductModelID INT)
RETURNS @T TABLE (ProductDescription XML)
AS
BEGIN
    INSERT @T
    SELECT CatalogDescription.query('
declare namespace PD="https://www.adventure-works.com/schemas/products/description";
                    //PD:ProductDescription  ')
    FROM Production.ProductModel
    WHERE ProductModelID = @ProductModelID

    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 dbo.MyUDF;
GO

CREATE FUNCTION MyUDF (@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.MyUDF(19);
SELECT @x;

Related content