FOR XML support for the xml data type
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;
This is the result. 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 (@ProudctModelID 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 = @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 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;
See also
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for