-1

I have a SQL query I am using to export to XML that works great:

SELECT remarks AS comments from Table1

I have no control over table and am only perform SELECT to it. The challenge I am facing is that some of the records have line breaks in this free text column that is causing the XML to "break".

Is there a way within the query itself to suppress or remove the line breaks and any other potentially bad characters?

Rocco The Taco
  • 3,695
  • 13
  • 46
  • 79
  • 1
    Why not just manipulate the data on your end? – BenM Jan 10 '14 at 16:50
  • Two options: @BenM's comment (i.e. use whichever language you're using to generate the XML to manipulate the string returned), or use SQL string functions (e.g. in MySQL: http://dev.mysql.com/doc/refman/5.0/en/regexp.html#operator_regexp) – ChrisW Jan 10 '14 at 16:53
  • I want to keep it within the query and output just the xml, no post-processing...clean, neater that way and let the MySQL server do the work @BenM – Rocco The Taco Jan 10 '14 at 16:55

1 Answers1

1

Although the best way to handle this is to stop bad data from going in, with the second being to have your code clean it up after reading from the database; you should be able to do something like:

SELECT REPLACE(COLUMN_NAME, '<br/>', '') AS comments FROM Table1;

This removes the breaks and replaces them with nothing.

Shawn
  • 3,583
  • 8
  • 46
  • 63