If you’re getting error Msg 6855 in SQL Server that reads “Inline schema is not supported with FOR XML PATH“, it’s because you’re trying to add an inline schema to an XML document that you’re generating using PATH mode with the FOR XML clause.

As the message alludes to, PATH mode doesn’t support the ability to create an inline schema when using the FOR XML clause.

To fix this issue, either use a different mode to generate the XML with an inline schema (specifically, use either AUTO or RAW mode), or don’t generate an inline schema at all.

Example of Error

Here’s an example of code that produces the error:

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.

As the error states, we can’t generate inline schemas when using PATH mode.

Solution 1

If we must generate an inline schema, we’ll need to change the mode to either AUTO or RAW:

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

Result:

<schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet6" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet6" 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:SqlRowSet6" PetId="1" DOB="2020-11-20" />
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet6" PetId="2" />
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet6" PetId="3" DOB="2018-10-01" />

This time I used AUTO mode, and the inline schema was generated as specified.

Solution 2

Another option is to generate the XML document without an inline schema:

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

Result:

<row>
	<PetId>
		1
	</PetId>
	<DOB>
		2020-11-20
	</DOB>
</row>
<row>
	<PetId>
		2
	</PetId>
</row>
<row>
	<PetId>
		3
	</PetId>
	<DOB>
		2018-10-01
	</DOB>
</row>