SQL FOR XML PATH returning 'illegal name character' error [duplicate]

Question

I am trying to use the FOR XML PATH "trick" to create one line of text out of several rows, e.g. like in this question: How to concat many rows into one string in SQL Server 2008?

However, SQL Server is returning an error message that says

XML parsing: line 1, character 53, illegal name character

I am unable to figure out what is causing this error. Probably there is something in my data that is does not like... The query in question is a correlated subquery, and is resembling this:

(select my_text_field as [text()] 
  from child_table 
  where foreign_key = master_table.id 
  order by some_sequence 
  for xml path ('')) as comment

The sheer magnitude of the data involved makes it hard to manually look through the fields for oddities.

Any hints as to how I could resolve this? I was under the impression that FOR XML PATH should be able to escape illegal characters by itself, but maybe not..?

That isn't what the accepted answer in the linked question is doing though? What is the character in position 53? — Thom A
– Thom A ♦, Commented Jul 22, 2019 at 11:13
@Larnu As far as I understand the question I linked, it does not address this problem. I am not sure what is in position 53 - the query runs for some 10 minutes before coming up with this error. — Eyvind
– Eyvind, Commented Jul 22, 2019 at 11:15
Try using TYPE and value as the answer does; does that solve the problem? (You'll probably want to remove the alias is the subquery too.) — Thom A
– Thom A ♦, Commented Jul 22, 2019 at 11:16
Which version of SQL Server are you using? If it's 2017 or newer, STRING_AGG is worth looking into. — reembank
– reembank, Commented Jul 22, 2019 at 11:42

DarkRob · Accepted Answer · 2019-07-22 11:35:08Z

0

Try this

    SELECT STUFF((SELECT N',' + my_text_field 
      FROM child_table 
      where foreign_key = master_table.id 
      order by some_sequence  
      FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)')
    ,1, 1, N'');

answered Jul 22, 2019 at 11:35

DarkRob

3,8231 gold badge12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL FOR XML PATH returning 'illegal name character' error [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related