I have a query that needs to return text as valid XML. I also need to restrict the length of the text to a specific number of characters. I adapted a function that performs the conversion and outputs valid XML as shown below
ALTER FUNCTION [dbo].[ReplaceForbiddenXMLChars] (@MyField VARCHAR(MAX), @Len INT)
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @rtn VARCHAR(MAX) = NULL;
IF @MyField IS NOT NULL
SET @rtn = LEFT((SELECT @MyField FOR XML PATH('')),@Len);
RETURN @rtn
END
The function works well most of the time, but I have come across an issue where the XML may be truncated resulting in invalid XML, for example:
SELECT dbo.[ReplaceForbiddenXMLChars]( 'Address: Abbey Fruit & Veg Ltd, 1234234 Mulberry Road, Chinatown, BB7 DNBD - Kingsbury Fruit & Veg',100)
Produces: Address: Abbey Fruit & Veg Ltd, 1234234 Mulberry Road, Chinatown, BB7 DNBD - Kingsbury Fruit &am
This is invalid due to the @am at the end.
I can't think of a way of reducing the size of this field while producing valid XML.
Any help appreciated.