If you’re getting error Msg 3625 in SQL Server that reads “‘Inline XSD for FOR XML EXPLICIT’ is not yet implemented“, it’s because you’re trying to add an inline schema to an XML document that you’re generating using EXPLICIT mode with the FOR XML clause.

As the message alludes to, SQL Server doesn’t yet support inline XSD schemas when using EXPLICIT mode of the FOR XML clause (at least, not at the time of this writing).

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 (i.e. remove XMLSCHEMA from the query).

Example of Error

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

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 19
'Inline XSD for FOR XML EXPLICIT' is not yet implemented.

As the error suggests, we can’t generate inline schemas when using EXPLICIT mode (at least not in the current version of SQL Server).

Solution 1

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

Here’s an example of a query that uses AUTO mode:

SELECT
    Pets.PetId,
    Pets.PetName,
    PetTypes.PetType
FROM Pets 
    INNER JOIN PetTypes ON Pets.PetTypeId = PetTypes.PetTypeId
ORDER BY Pets.PetId, Pets.PetName
FOR XML AUTO, XMLSCHEMA;

Result:

<schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet31" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet31" 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>
			<sequence>
				<element ref="schema:PetTypes" minOccurs="0" maxOccurs="unbounded" />
			</sequence>
			<attribute name="PetId" type="sqltypes:int" use="required" />
			<attribute name="PetName" use="required">
				<simpleType>
					<restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
						<maxLength value="60" />
					</restriction>
				</simpleType>
			</attribute>
		</complexType>
	</element>
	<element name="PetTypes">
		<complexType>
			<attribute name="PetType" use="required">
				<simpleType>
					<restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
						<maxLength value="60" />
					</restriction>
				</simpleType>
			</attribute>
		</complexType>
	</element>
</schema>
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet31" PetId="1" PetName="Fluffy">
	<PetTypes PetType="Cat" />
</Pets>
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet31" PetId="2" PetName="Fetch">
	<PetTypes PetType="Dog" />
</Pets>
<Pets xmlns="urn:schemas-microsoft-com:sql:SqlRowSet31" PetId="3" PetName="Scratch">
	<PetTypes PetType="Cat" />
</Pets>

Solution 2

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

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;

Result:

<Pet PetId="1">
	<Details PetName="Fluffy" PetType="Cat" />
</Pet>
<Pet PetId="2">
	<Details PetName="Fetch" PetType="Dog" />
</Pet>
<Pet PetId="3">
	<Details PetName="Scratch" PetType="Cat" />
</Pet>