how to unescape

• shanghaigirl-1125377

  Ten Centuries

  Points: 1231

  More actions

  August 11, 2010 at 8:29 am

  #387551

  I need to display result like :

  1 <A>, <B>, <C>, <D>

  2 <E>, <Z>, <U>

  3 <G>, <T>

  Not like:

  1<A>, <B>, <C>, <D>,

  2<E>, <U>, <Z>,

  3<G>, <T>,

  declare @T table

  (col1 int,

  col3 char(3))

  insert into @T

  select 1, '<A>' union all

  select 1, '<B>' union all

  select 1, '<C>' union all

  select 1, '<D>' union all

  select 2, '<E>' union all

  select 2, '<Z>' union all

  select 2, '<U>' union all

  select 3, '<G>' union all

  select 3, '<T>'

  --select * from @T

  SELECT

  col1,

  (SELECT col3 + ', ' AS [text()]

  FROM @T a

  WHERE a.col1 = b.col1

  ORDER BY col3

  FOR XML PATH(''))

  FROM @T b

  GROUP BY col1

  Thanks for help

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  August 11, 2010 at 8:36 am

  #1205395

  You can do this:

  declare @T table

  (col1 int,

  col3 char(3))

  insert into @T

  select 1, '<A>' union all

  select 1, '<B>' union all

  select 1, '<C>' union all

  select 1, '<D>' union all

  select 2, '<E>' union all

  select 2, '<Z>' union all

  select 2, '<U>' union all

  select 3, '<G>' union all

  select 3, '<T>'

  --select * from @T

  SELECT col1, LEFT(res, LEN(res) - 1)

  FROM

  (

  SELECT

  col1,

  (SELECT col3 + ', ' AS [text()]

  FROM @T a

  WHERE a.col1 = b.col1

  ORDER BY col3

  FOR XML PATH('')) res

  FROM @T b

  GROUP BY col1

  ) a

  Please note: concatenating strings using FOR XML PATH will not perform well for large datasets...

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• shanghaigirl-1125377

  Ten Centuries

  Points: 1231

  More actions

  August 11, 2010 at 2:11 pm

  #1205703

  thanks. but it didn't give me what I need.

  probably when I copied, it unescaped again.

  this was the result I got:

  1<A>, <B>, <C>, <D>,

  2<E>, <U>, <Z>,

  3<G>, <T>,

  I need it like "<" instead of "<",

  and ">" instead of ">".

• shanghaigirl-1125377

  Ten Centuries

  Points: 1231

  More actions

  August 11, 2010 at 2:24 pm

  #1205712

  OK, I am trying again. I have to put space between chars so that it won't unescape when it was posted.

  It gave me

  1& l t ;A & g t ;, & l t ; B & g t ;, & l t ;C & g t ;, & l t ; D & g t ;,

  2& l t ;E & g t ;, & l t ; U & g t ;, & l t ;Z & g t;,

  3& l t ;G & g t ;, & l t ; T & g t ;,

  I need "<", not "& l t ;" and " > ", not " & g t ;"

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 11, 2010 at 2:51 pm

  #1205724

  Web browsers unescape the characters for you. In order to display the escaped characters, you have to escape the ampersands that are produced by your query.

  col1(No column name)

  1<A>, <B>, <C>, <D>,

  2<E>, <U>, <Z>,

  3<G>, <T>,

  Your strings are not well-formed XML, because they look like elements that don't have closing tags. Because they're not well-formed, SQL treats them as varchar and escapes certain characters. Getting around this is fairly difficult. You'll probably have to replace all of the escaped characters with their unescaped forms by using the Replace() function. Just hope that you catch all of the escaped characters.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• shanghaigirl-1125377

  Ten Centuries

  Points: 1231

  More actions

  August 11, 2010 at 3:06 pm

  #1205732

  Thanks a lot.

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  August 11, 2010 at 3:45 pm

  #1205746

  I think the XML function just particularly dislikes the "<" and ">" since those characters have such significance in the XML structure. If you just replace those after the XML function operates you should be fine. I tried quite a few other special characters and those were the only ones I found to be a problem.

  SELECT col1,

  REPLACE(REPLACE((SELECT col3 + ', ' AS [text()]

  FROM @T a

  WHERE a.col1 = b.col1

  ORDER BY col3

  FOR XML PATH('')),'& l t ;','<'),'& g t ;','>')

  FROM @T b

  GROUP BY col1

  *edit: take out the spaces ... how the hell do you get this forum to just show the actual text you type I do not know

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• shanghaigirl-1125377

  Ten Centuries

  Points: 1231

  More actions

  August 12, 2010 at 7:26 am

  #1205996

  thank you so much. it worked for me.