Error - FOR XML could not serialize the data for node 'NoName'

Question

Error - FOR XML could not serialize the data for node 'NoName'

brenda grossnickle 206

I am using FOR XML to concatenate varchar data. The data came from user input so it could have garbage characters. Getting the "Error - FOR XML Could Not Serialize the data for node NoName". Not really picky about what to do with the bad data, could stay, could be replaced with '' empty string, don't really care. There are just a couple of bad data instances out of 77k rows. Just need the FOR XML to work.

below is some test data and to get it to work i added "ltrim(rtrim((SELECT [col1] AS [*] FOR XML Path(''))))" where it use to be just "col1". then added the update statements.

Really out of my depth here. what gotchas or suggestions do you have for my solution?

 
/***** create test data *****/
 
drop table temp.#test
 
create table #test (recid int identity, col1 varchar(126))
 
insert into #test (col1) values
 
                ('aaa'),
 
    ('ccc' + cast(0x1A as varchar(30)) + 'bbb'),
 
                ('"ddd <e' )
 
/****** Use STUFF, FOR XML to concatenate rows *******/
 
drop table temp.#list_complete
 
declare @SQLStuff nvarchar(max) = ''
 
SELECT @SQLStuff =
 
    STUFF((SELECT ';      ' + ltrim(rtrim((SELECT [col1] AS [*] FOR XML Path(''))))
 
           FROM #test
 
           FOR XML PATH (''), TYPE).value('(/text())[1]', 'varchar(max)'), 1, 2, '')
 
select * into #list_complete from
 
                (select cast( @SQLStuff as varchar(128)) as codes)a
 
/******* update XML escape characters ********/
 
update #list_complete set codes = replace(codes, '&lt;', '<')
 
update #list_complete set codes = replace(codes, '&gt;', '>')
 
update #list_complete set codes = replace(codes, '&apos;', '''')
 
update #list_complete set codes = replace(codes, '&quot;', '"')
 
update #list_complete set codes = replace(codes, '&amp;', '&')
 
select * from #list_complete

Viorel 122.2K Reputation points

2025-05-17T22:17:28.2733333+00:00

Is it possible to use select @SQLStuff = string_agg(col1, '; ') within group (order by recid) from #test and to remove the UPDATE statements?

2 answers

Your answer

Viorel 122.2K Reputation points

2025-05-17T22:17:28.2733333+00:00

Is it possible to use select @SQLStuff = string_agg(col1, '; ') within group (order by recid) from #test and to remove the UPDATE statements?

Answer 1

Erland Sommarskog 121.3K MVP Volunteer Moderator

As Viorel says, the best solution string_agg; then you don't have to fight these restrictions. But I have a recollection that you need to support anitque versions of SQL Server. string_agg reqiures SQL 2017.

I'm leaning towards that it is better to get the garbage out before you do the FOR XML thing. Here is code to remove all control characters from 0 to 31:

drop table temp.#test
 
create table #test (recid int identity, col1 varchar(126))
 
insert into #test (col1) 
   values ('aaa'),
          ('ccc' + cast(0x1A as varchar(30)) + 'bbb'),
          ('"ddd <e' )

-- Code to remove control characters
declare @pat char(7) = concat('%[', char(0), '-', char(31) + ']%')
while 1 = 1
begin
   update #test
   set    col1 = substring(col1, 1, patindex(@pat collate French_BIN2, col1) - 1) +
                 substring(col1, patindex(@pat collate French_BIN2, col1) + 1, len(col1)) 
   where  col1 LIKE @pat collate French_BIN2
   if @@rowcount = 0
      break
end
 
/****** Use STUFF, FOR XML to concatenate rows *******/
drop table temp.#list_complete
declare @SQLStuff nvarchar(max) = ''
SELECT @SQLStuff =
    STUFF((SELECT ';      ' + ltrim(rtrim(col1))
           FROM #test
           FOR XML PATH (''), TYPE).value('(/text())[1]', 'varchar(max)'), 1, 2, '')
                (select cast( @SQLStuff as varchar(128)) as codes)a
 
SELECT * FROM #list_complete

Don't worry about the French. We want a binary collation, and French is shorter than Latin1_General.

The exact range of characters may need some fine-tuning. There may be more characters to add. On the other hand, you may want to permit for tag, carriage return and line feed.

Grossnickle, Brenda 120 Reputation points

2025-05-19T18:37:35.4033333+00:00

yes, i have to support 2012 to current and some of the 2012 have compatibility levels pre-2012. My first choice would be to remove all the characters that cause FOR XML to fail. But I am not exactly what are those characters. Is it only the 0-31 control characters or could there be others? We support 300+ businesses each with their own sql server. Each time a new code has to be added that is a fix release to 300 sql servers - no bueno. This code fix has to work for any characters. Again, i am not too concerned about how the invalid characters look. There is a very small percentage over a large set of non-critical data. Are the 0-31 control characters the only ones that can cause FOR XML to fail?
Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-05-19T20:55:25.7033333+00:00

I'm afraid that I don't know on the top of my head which characters are illegal, you would have to test yourself. Here is a tip for doing this: On SQL 2022, you can use generate_series to generate all numbers to try from 0 to 65535. And since this only for testing, you don't have to care about those antique SQL 2012 customers.

Or do this: Fork the code, to use string_agg if it is available. You will need to hide string_agg in an inner module or dynamic SQL to avoid compilation errors on older versions. In FOR XML, you handle 0-31, and if a customer with an older version runs into an issue with some other character, you tell them that it is time to upgrade. Of course, since this is a business decision, this is something you need to have your bosses, and maybe a few more bosses up, to agree on.

And, after all, how long have you've been using this FOR XML code? How long time did it take before this issue came up? How much time and cost should your company spend on this, when there is a bulletproof solution for newer versions of SQL Server?

Answer 2

Hi @brenda grossnickle,

You can use the following T-SQL to identify rows with questionable characters for the XML data type. It will work starting from SQL Server 2012 onwards.

Casting by using the TRY_CAST(... AS XML) does all magic. The questionable strings will have NULL values at the end for your analysis.

-

- DDL and sample data population, start
DECLARE @test table (recid INT IDENTITY PRIMARY KEY, col1 VARCHAR(126));
INSERT @test (col1) VALUES 
('aaa'),
('ccc' + cast(0x1A as varchar(30)) + 'bbb'),
('"ddd <e');
-- DDL and sample data population, end
  
-- Code to identify 'bad' strings in a row
WITH rs AS
(
	SELECT * 
		, TRY_CAST('<r>' + col1 + '</r>' AS XML) AS xml_type
	FROM @test
)
SELECT * FROM rs
WHERE xml_type IS NULL;

Share via

Error - FOR XML could not serialize the data for node 'NoName'

2 answers

Your answer