FOR XML PATH and Carriage Returns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FOR XML PATH and Carriage Returns

Author

Topic

T.C.
Starting Member

2 Posts

Posted - 2010-01-11 : 12:47:46

I'm trying to use the FOR XML PATH clause to create a list of items delimited with a carriage return and line feed. I can't get it to work. My query looks something like this:


SELECT 
  CONTACT_ID, 
  CONTACT_SUMMARY = SUBSTRING( 
    ( 
      SELECT 
        CHAR(13) + CHAR(10) + CONTACT_DETAIL AS [text()] 
      FROM 
        CONTACT_LS A
      WHERE 
        A.CONTACT_ID = B.CONTACT_ID 
      ORDER BY
        POS
      FOR XML PATH ('') 
    ), 3, 1000) 
FROM 
  CONTACT B

Instead of clean line breaks, I get the code "#xD" embedded in the output string. ("#xD" is the XML escape sequence for a carriage return.)

What am I doing wrong?

-TC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 12:55:09

what about this?


SELECT 
  CONTACT_ID, 
  CONTACT_SUMMARY = SUBSTRING( 
    CONVERT(varchar(max),( 
      SELECT 
        CHAR(13) + CHAR(10) + CONTACT_DETAIL AS [text()] 
      FROM 
        CONTACT_LS A
      WHERE 
        A.CONTACT_ID = B.CONTACT_ID 
      ORDER BY
        POS
      FOR XML PATH (''),TYPE 
    )), 3, 1000) 
FROM 
  CONTACT B

T.C.
Starting Member

2 Posts

Posted - 2010-01-11 : 14:18:26

Visakh,

Thanks for the help. I'll give it a try.

-TC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 01:09:19

good luck

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-12 : 05:36:31

[code]
SELECT
CONTACT_ID,
CONTACT_SUMMARY = SUBSTRING(
CONVERT(varchar(max),(
SELECT
CHAR(13) + CHAR(10) + CONTACT_DETAIL AS [text()]
FROM
CONTACT_LS A
WHERE
A.CONTACT_ID = B.CONTACT_ID
ORDER BY
POS
FOR XML PATH (''),TYPE
).value('.[1]','varchar(max)')
), 3, 1000)
FROM
CONTACT B
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

mjkoskinen
Starting Member

5 Posts

Posted - 2011-07-05 : 17:02:08

I'm having a similar problem. My query is as follows:

SELECT 
	CI.EOVID as "@EOVid",
	CI.DefName as "@DefinitionName",
	(CONVERT(varchar(max),( 
	SELECT 
			CAST('<' +
			REPLACE(UsefulColumnName, ' ','') + '>' + 
			REPLACE(
			REPLACE(
			REPLACE(ColumnValue,'<','')
			,'>','')
			,'&','+')
			 + '</' +
			REPLACE(UsefulColumnName, ' ','') + '>' + CHAR(13) + CHAR(10)
			AS XML) FROM #ContentItemParts CIP
		WHERE CI.EOVID = CIP.EOVID
		FOR XML PATH(''), TYPE)))
		
	FROM #ContentItems CI
	FOR XML PATH('Root'), ROOT('ContentItems');

I am not getting any line breaks in the resulting XML. The problem is that if no line breaks are generated then they are automatically added after a particular length causing the XML to be corrupt. In otherwords I'm getting line breaks in random locations and not after an XML node. Any ideas on how to get these intentional line breaks to show or any alternatives?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources