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="https://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="https://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>
Returning 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:
CREATE FUNCTION 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 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
See Also
Reference
FOR XML Support for Various SQL Server Data Types