0

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..?

Eyvind
  • 5,221
  • 5
  • 40
  • 59
  • That isn't what the accepted answer in the linked question is doing though? What is the character in position 53? – Thom A Jul 22 '19 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 Jul 22 '19 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 Jul 22 '19 at 11:16
  • can you post some of your sample input data. – DarkRob Jul 22 '19 at 11:28
  • it might be something in your string which is not accepted in `xml` format. – DarkRob Jul 22 '19 at 11:28
  • See [this answer](https://dba.stackexchange.com/questions/207371/please-explain-what-does-for-xml-path-type-value-nvarcharmax) for a better technique. – Dan Guzman Jul 22 '19 at 11:37
  • 1
    Which version of SQL Server are you using? If it's 2017 or newer, STRING_AGG is worth looking into. – reembank Jul 22 '19 at 11:42
  • We're on 2016, but perhaps it might be possible to upgrade; thanks! – Eyvind Jul 22 '19 at 12:43
  • @DarkRob No, this is medical data, which for obvious reasons I cannot post. – Eyvind Jul 22 '19 at 12:43
  • @Eyvind: have you tried my posted solution, if it is not working then kindly post the error of that query. – DarkRob Jul 22 '19 at 17:15

1 Answers1

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'');
DarkRob
  • 3,843
  • 1
  • 10
  • 27