How to fix Error 6813 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 6813 – “FOR XML EXPLICIT cannot combine multiple occurrences of ID”

Why This Error Happens

This error occurs when a FOR XML EXPLICIT query contains a column marked with the id directive appearing more than once for the same tag number in the UNION-based result set. SQL Server’s XML EXPLICIT mode requires a single, unique identifier column per tag to correctly construct the parent-child hierarchy, and duplicate id declarations break this mapping logic. This typically stems from a malformed query structure, often introduced during manual query edits, ORM-generated SQL, or a migration script ported from an older SQL Server version without validation against SQL Server 2025’s stricter XML parser.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Query

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
  • Use the SQL Server Error Log (Management > SQL Server Logs) or Extended Events session to capture the exact query text that triggered Error 6813.
  • Search the query body for the FOR XML EXPLICIT clause and identify all AS [TagNumber!TagName!AttributeName!Directive] column aliases.

Step 2: Audit the Column Directives

  • Review every column alias formatted as [Tag!TagName!Column!id] across all SELECT statements joined by UNION ALL.
  • Confirm that only one column per unique Tag number uses the !id directive; all other identifying columns for that tag should omit the directive or use hide instead.
  • Example fix — change:
  1 AS Tag, NULL AS Parent,
  CustomerID AS [Customer!1!CustomerID!id],
  OrderID AS [Customer!1!OrderID!id]  -- duplicate id, remove this

to:

  1 AS Tag, NULL AS Parent,
  CustomerID AS [Customer!1!CustomerID!id],
  OrderID AS [Customer!1!OrderID]

Step 3: Rebuild and Test the Query Incrementally

  • Break the UNION ALL query into individual SELECT blocks and run each in isolation in SSMS to confirm correct tag/column mapping.
  • Reassemble the blocks one at a time, re-running the full query after each addition to catch the exact block introducing the duplicate id.
  • Save a corrected version of the stored procedure or script using ALTER PROCEDURE or CREATE OR ALTER PROCEDURE.

Step 4: Clear Cached Execution Plans

  • If the procedure was recently altered but the error persists, flush the stale plan from cache:
  DBCC FREEPROCCACHE;
  • Alternatively, target a single procedure’s plan using sp_recompile 'YourProcedureName'; to avoid a full cache flush on production servers.

Alternative or Verification Steps

  • Re-run the corrected query directly in SSMS and confirm it returns valid XML output without Error 6813; check the Results to Grid/XML pane for proper nested element structure.
  • As an alternative, consider migrating the logic to FOR XML PATH or FOR XML AUTO with TYPE directives, which offer simpler syntax and avoid the strict tag/id pairing rules enforced in EXPLICIT mode.