SQL Server FOR XML can fail when text contains a control character that XML 1.0 does not permit. For the error that identifies 0x0002, locate the affected rows with NCHAR(2) and remove that exact STX character in the export query.
Last updated: November 13, 2026.
-- Find rows containing U+0002 START OF TEXT (STX).
SELECT archive_id, notes
FROM dbo.HistoricArchive
WHERE CHARINDEX(NCHAR(2), notes COLLATE Latin1_General_100_BIN2) > 0;
-- Clean only the exported value; leave the archive unchanged.
SELECT
archive_id AS [@id],
REPLACE(notes COLLATE Latin1_General_100_BIN2, NCHAR(2), N'') AS [notes]
FROM dbo.HistoricArchive
FOR XML PATH('record'), ROOT('archive'), TYPE;The binary collation makes the character comparison explicit. Apply REPLACE() to the exported expression rather than updating an immutable archive table.
Use the code point from the error
Microsoft lists the serialization failure in its SQL Server 6000–6999 error reference. The hexadecimal value identifies the character that must be investigated. 0x0002 is U+0002 START OF TEXT, so NCHAR(2) creates the matching Unicode value.
First count and sample affected rows. Confirm which columns contain the character and whether removing it is appropriate for the export. Test the cleaned expression with the real receiving parser, not only SQL Server Management Studio. If the data has contractual or forensic value, preserve the original and log the cleaning rule and affected row count.
Do not confuse data with XML names
Microsoft’s invalid XML character guidance distinguishes invalid data characters from escaping SQL identifiers into XML names. Name escapes such as _x0020_ do not make U+0002 valid character data for an XML 1.0 parser.
Do not drop the TYPE directive merely to make the query appear to finish. A non-type result may expose an entitized diagnostic form, but a conforming XML 1.0 consumer can still reject it. If errors name other code points, inventory each one and build a reviewed export-cleaning rule. Tabs, line feeds, and carriage returns are valid XML whitespace and should not be removed blindly.
