XQuery - XML column

• mviggers

  Old Hand

  Points: 339

  More actions

  May 6, 2015 at 5:14 am

  #317927

  Hi

  I have following XQuery:

  declare @xmldoc as xml

  select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'

  select @xmldoc.value('(/Text)[1]','varchar(max)')

  Result is: "This is firstlineThis is second lineThis is third line"

  My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. Anyone who knows how to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?

  Thanks in advance 🙂

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  May 6, 2015 at 6:43 am

  #1795909

  Maybe this?

  declare @xmldoc as xml= '<Text>This is firstline<Break></Break>This is second line<Break></Break>This is third line</Text>';

  declare @break char(1) = char(10);

  select @xmldoc.query('for $c in /Text/text() return concat($c,sql:variable("@break"))').value('.','varchar(max)');

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 6, 2015 at 3:59 pm

  #1796075

  or...

  declare @xmldoc as xml

  select @xmldoc = REPLACE('<Text>This is firstline<Break />This is second line<Break />This is third line</Text>','<Break />',CHAR(10))

  select @xmldoc.value('(/Text)[1]','varchar(max)')

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001