When using the FOR XML clause in a query in SQL Server, we can generate an XML document either with or without an inline schema.

To generate it with an inline XSD schema simply include the XMLSCHEMA argument. We can alternatively include the namespace if we want to specify a particular namespace.

At the time of writing, the XMLSCHEMA argument is only available when in either AUTO or RAW mode.

Example

Here’s an example to demonstrate:

SELECT
    PetId,
    DOB
FROM Pets
ORDER BY PetId
FOR XML AUTO, XMLSCHEMA;

Result:

<schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet4" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet4" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
	<import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
	<element name="Pets">
		<complexType>
			<attribute name="PetId" type="sqltypes:int" use="required" />
			<attribute name="DOB" type="sqltypes:date" />
		</complexType>
	</element>
</schema>
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" PetId="1" DOB="2020-11-20" />
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" PetId="2" />
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" PetId="3" DOB="2018-10-01" />

Specify the Namespace

The target namespace of the schema document that describes the shape of the FOR XML result contains a fixed portion and a numeric portion that increments automatically. This numeric portion increments each time we run the query. We can see this in the previous example, where the namespace ends with SqlRowSet4. The 4 is there, because I ran it four times. The next time I run it, it will increment to SqlRowSet5, and so on.

If this isn’t desirable, we can change it so that it uses a fixed namespace.

SELECT
    PetId,
    DOB
FROM Pets
ORDER BY PetId
FOR XML AUTO, XMLSCHEMA ('urn:schemas-microsoft-com:sql:SqlRowSet1');

Result:

<schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
	<import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
	<element name="Pets">
		<complexType>
			<attribute name="PetId" type="sqltypes:int" use="required" />
			<attribute name="DOB" type="sqltypes:date" />
		</complexType>
	</element>
</schema>
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" PetId="1" DOB="2020-11-20" />
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" PetId="2" />
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1" PetId="3" DOB="2018-10-01" />

Using the Wrong Mode

As mentioned, the XMLSCHEMA argument is only available when in either AUTO or RAW mode at the time of writing. In the above examples, I ran it in AUTO mode.

Here’s what happens when I try to use XMLSCHEMA in PATH mode:

SELECT
    PetId,
    DOB
FROM Pets
ORDER BY PetId
FOR XML PATH, XMLSCHEMA;

Result:

Msg 6855, Level 16, State 1, Line 1
Inline schema is not supported with FOR XML PATH.

I actually get a different error when I use EXPLICIT mode:

SELECT
    1 AS Tag,
    NULL AS Parent,
    p.PetId AS [Pet!1!PetId],
    NULL AS [Details!2!PetName],
    NULL AS [Details!2!PetType]
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    p.PetId,
    p.PetName,
    pt.PetType
FROM Pets p 
    INNER JOIN PetTypes pt ON p.PetTypeId = pt.PetTypeId
ORDER BY [Pet!1!PetId], [Details!2!PetName]
FOR XML EXPLICIT, XMLSCHEMA;

Result:

Msg 3625, Level 16, State 17, Line 1
'Inline XSD for FOR XML EXPLICIT' is not yet implemented.

This error implies that XMLSCHEMA may be supported in future versions of SQL Server.

XDR Schemas

There’s also an XMLDATA directive that generates an XDR schema when using FOR XML. However, this directive is now deprecated, and Microsoft recommends that we use the XMLSCHEMA method above.

More Information

See the Microsoft documentation for more information on generating an inline XSD schema in SQL Server.