FOR XML function converting "&" to "&"

Question

Post reply

FOR XML function converting "&" to "&"

James Millar-305032

Ten Centuries

Points: 1047
More actions
January 8, 2014 at 1:09 pm

#396074

Hi,
I have created a function using FOR XML to collate all hotel room details, but I have an issue whereby the function returns the & character as & - for example the room type Bed & Breakfast" would appear as Bed "& amp;" Breakfast.
Can anyone tell me how I can update the function to recognize the "&" character correctly?
My function:
CREATE FUNCTION DBA.fn_ListHotelRoomDetails( @strBranchCode CHAR( 2 ), @lFoldNo INT, @nFoldItemID SMALLINT )
RETURNS VARCHAR( 8000 )
BEGIN
DECLARE @l_strRetVal VARCHAR( 8000 )
SELECT @l_strRetVal =
str.strHotelBookingDetails
FROM(
SELECT strHotelBookingDetails =
STUFF(( SELECT CAST( CHAR ( 10 ) +
CASE WHEN strSdRoomTypeCode_FD <> '' THEN
COALESCE( RT.RoomTypeName_FD, RT1.RoomTypeName_FD, strSdRoomTypeCode_FD )
ELSE
''
END
+
CASE WHEN strSdMealCode_FD <> '' THEN
CASE WHEN strSdRoomTypeCode_FD <> '' THEN
', '
ELSE
' '
END
+ COALESCE( MC.MealName_FD, MC1.MealName_FD, strSdMealCode_FD ) + '.'
ELSE
''
END
AS VARCHAR( 8000 ))
FROM
DBA.SegmentDetail_TB AS SD WITH( NOLOCK )
JOIN DBA.FoldItems_TB AS FI
ON SD.BranchCode_FD = FI.strBBranchCode_FD
AND SD.FolderNo_FD = FI.lFFoldNo_FD
AND SD.nFiFoldItemID_FD = FI.nFiFoldItemID_FD
AND FI.strFiType_FD = 'HOT'
LEFT OUTER JOIN DBA.RoomType_TB AS RT
ON RT.RoomTypeCode_FD = SD.strSdRoomTypeCode_FD
AND RT.lVVendCode_FD = FI.lFiItinVendID_FD
AND RT.IsDeleted_FD = 0
LEFT OUTER JOIN DBA.RoomType_TB AS RT1
ON RT1.RoomTypeCode_FD = SD.strSdRoomTypeCode_FD
AND RT1.lVVendCode_FD = -1
AND RT1.IsDeleted_FD = 0
LEFT OUTER JOIN DBA.MealCode_TB AS MC
ON MC.MealCode_FD = SD.strSdMealCode_FD
AND MC.lVVendCode_FD = FI.lFiItinVendID_FD
AND MC.IsDeleted_FD = 0
LEFT OUTER JOIN DBA.MealCode_TB AS MC1
ON MC1.MealCode_FD = SD.strSdMealCode_FD
AND MC1.lVVendCode_FD = -1
AND MC1.IsDeleted_FD = 0
WHERE
SD.BranchCode_FD = @strBranchCode
AND SD.FolderNo_FD = @lFoldNo
AND SD.nFiFoldItemID_FD = @nFoldItemID
ORDER BY nSdSortOrder_FD
FOR XML PATH( '' )), 1, 1, '' )
) str
RETURN @l_strRetVal
END
GO
Thanks,
James

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Change

FOR XML PATH( '' )), 1, 1, '' )

To

FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

James Millar-305032 Ten Centuries Points: 1047 More actions · Answer 2

Ah yes Luis... That worked a treat!

Thank you - much appreciated!

James