Converting VARCHAR to XML with a size restriction

Question

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.

Answer 1

You can check if the XML string you created is valid and if it is not you can try to remove the last (perhaps incomplete) entity and try again. There can be other issues with the string that makes the XML invalid even if it is created using for xml path.

CREATE OR ALTER FUNCTION [dbo].[ReplaceForbiddenXMLChars] (@MyField VARCHAR(MAX), @Len INT)
RETURNS VARCHAR(MAX) AS
BEGIN

DECLARE @rtn VARCHAR(MAX) = NULL;

IF @MyField IS NOT NULL 
BEGIN
    SET @rtn = LEFT((SELECT @MyField FOR XML PATH('')),@Len);

    -- Check if xml is valid
    IF TRY_CAST(@rtn AS XML) IS NULL
    BEGIN
      -- Guess that it is a broken entity at the end of the string and remove it
      SET @rtn = LEFT(@rtn, LEN(@rtn) - CHARINDEX('&', REVERSE(@rtn)));
      
      -- Check if xml is valid
      IF TRY_CAST(@rtn AS XML) IS NULL
        -- Something is still wrong
        SET @rtn = NULL;
    END;
END;