FOR XML PATH command is altering data, trying to determine the cause.

• huston.dunlap

  SSCommitted

  Points: 1551

  More actions

  May 1, 2010 at 11:40 pm

  #386836

  I am doing some good old string concentatenation, and I've been reading up on using for XML Paths commands to do it.

  Here is some sample code I've used.

  SELECT documentID

  ,(SELECT @DELIMIT + [VALUE] FROM

  (SELECT DOC.documentID,CONT.ContentKey as FIELDNAME,CONT.CONTENTVALUE as VALUE

  FROM dbo.Document as DOC

  inner join dbo.DocumentContent as CONT

  on CONT.documentID = DOC.documentID

  AND DOC.PROCESSID = @ID) as s2

  WHERE s2.documentID= s1.documentID AND s2.FIELDNAME = S1.FIELDNAME

  ORDER BY documentID

  FOR XML PATH('')) as [VALUES]

  ,FIELDNAME

  FROM

  (SELECT DOC.documentID,CONT.ContentKey as FIELDNAME,CONT.CONTENTVALUE as VALUE

  FROM dbo.Document as DOC

  inner join dbo.DocumentContent as CONT

  on CONT.documentID = DOC.documentID

  AND DOC.PROCESSID = @ID) as s1

  GROUP BY s1.documentID,FIELDNAME

  The query is concentating my strings, however, it is also altering them. Here's an example:

  <bob.bobson@bob.com>

  BECOMES

  &(lt);bob.bobson@bob.com&(gt)

  *NOTE: The () are not actually in the text. I added them because the forum would not render my words properly without them.

  Is this expected behavior for this command, or is there something extra I am not doing?

  While there are other methods of concetating, this method so far has turned out to be the fastest so I'm hoping its fixable.

• WayneS

  SSC Guru

  Points: 95460

  More actions

  May 2, 2010 at 8:16 am

  #1160991

  As to the "Why", check out this link.

  One thing to note there is that you can utilize a processing-instruction (thanks Jason & Paul!). So, this code might help you out.

  declare @test-2 TABLE (RowID int IDENTITY, Data varchar(200))

  insert into @test-2

  select 'some regular text' UNION ALL

  select 'email: <bob.jones@domain.com>' UNION ALL

  select 'Tom & Jerry'

  select Data

  from @test-2

  FOR XML PATH('')

  select Data as [processing-instruction(Data)]

  from @test-2

  FOR XML PATH('')

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  May 2, 2010 at 10:09 am

  #1161002

  Here is a quick example (using WayneS sample code)

  declare @test-2 TABLE (RowID int IDENTITY, Data varchar(200))

  insert into @test-2

  select 'some regular text' UNION ALL

  select 'email: <bob.jones@domain.com>' UNION ALL

  select 'Tom & Jerry'

  select t.r.value('.[1]','varchar(512)')

  from (select Data+' '

  from @test-2

  FOR XML PATH(''),type) t(r)

  Here, I am making sure the concatenated string is returned as an xml fragment and then grabbing that as text into a varchar. This method eliminates the encoding problem you are seeing.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]