Device control code 4 and for xml auto

• brian.peyton

  SSC Rookie

  Points: 37

  More actions

  October 16, 2008 at 11:24 pm

  #72081

  I have a table with a varchar column with a string containing a , ascii code 0x14, device control code 4. Now it looks like a pilcrow (paragraph symbol) in notepad, and that's what I need to treat it as.

  When I do a "select * from x for xml auto" it escapes said character to . While this normally wouldn't be a problem, I want to be able to have a statement like this work:

  declare @test-2 xml;

  set @test-2 = (select top 1 testColumn from testruns for xml auto);

  However this returns the error: "XML parsing: line 1, character 21, illegal xml character". Note that this works fine without the assignment.

  How do I get it to deal with funky characters like this without filtering them out or something?

• brian.peyton

  SSC Rookie

  Points: 37

  More actions

  October 17, 2008 at 10:38 am

  #886507

  I've figured it out... that pilcrow isn't in the list of allowed characters in XML:

  http://www.w3.org/TR/2006/REC-xml-20060816/#charsets

  Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]

  So I'm going to create a case statement to deal with characters < x20 (minus the allowed ones) and replace them with valid unicode ones.