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

by

Troubleshooting Guide: SQL Server Error 6826 – “Every IDREFS or NMTOKENS Column Must Appear in a Separate SELECT Clause”

Why This Error Happens

This error occurs when a FOR XML EXPLICIT query attempts to combine multiple columns typed as IDREFS or NMTOKENS within the same SELECT statement branch, which violates the XML data type requirements for multi-value attribute columns. The SQL Server XML parser requires each IDREFS or NMTOKENS column to be isolated in its own SELECT clause because these data types represent space-separated token lists that cannot be merged or interleaved with other columns at the same tag/parent level. This is not a permissions or connectivity issue, but rather a syntax structure violation specific to how FOR XML EXPLICIT mode constructs hierarchical XML output.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Columns

  • Open SQL Server Management Studio (SSMS) 2025 and locate the exact query referenced in the error stack trace or application log.
  • Review the column aliasing syntax, specifically looking for columns formatted as TagName!TagNumber!AttributeName!IDREFS or !NMTOKENS.
  • Check if two or more of these typed columns exist under the same TagNumber grouping in a single SELECT statement.

Step 2: Separate the Columns into Distinct SELECT Clauses

  • Rewrite the query so that each IDREFS or NMTOKENS column resides in its own standalone SELECT block.
  • Use UNION ALL to join these separated SELECT statements back together, ensuring the TagNumber and Parent columns remain consistent across all unioned blocks.
  • Example fix pattern: Convert SELECT Tag, Parent, Col1!1!Attr1!IDREFS, Col2!1!Attr2!IDREFS FROM... into two separate SELECT statements joined by UNION ALL, one for Col1 and one for Col2.

Step 3: Validate Tag and Parent Consistency

  • Ensure the Tag and Parent integer values match exactly across all newly separated SELECT clauses so the XML hierarchy reconstructs correctly.
  • Confirm that NULL placeholders are inserted for non-relevant columns in each separated clause to maintain column count parity.
  • Re-run the query in a test environment (non-production database) before deploying to Windows Server 2025 production instances.

Step 4: Consider Migrating to FOR XML PATH or AUTO Mode

  • If the EXPLICIT mode logic has become overly complex, evaluate refactoring the query to use FOR XML PATH('') instead, which does not impose the same single-column restriction for token-list types.
  • Test output compatibility with downstream consumers (APIs, BizTalk, or legacy parsers) since PATH mode generates slightly different XML formatting than EXPLICIT.

Step 5: Apply Latest Cumulative Update

  • Verify you are running the latest Cumulative Update (CU) for SQL Server 2025 via SELECT @@VERSION; in a new query window.
  • Download and install any pending CU from the Microsoft Update Catalog if the build number is outdated, as XML parsing edge-case fixes are occasionally included in servicing releases.

Alternative or Verification Steps

  • Execute the corrected query using SET STATISTICS XML ON or simply run it standalone in SSMS to confirm the result set returns valid, well-formed XML without triggering Error 6826.
  • If the error persists after restructuring, temporarily substitute IDREFS/NMTOKENS typed columns with standard string type output and manually format the space-separated values using STRING_AGG() as a workaround.