Technical Article

XML Encode UDF

,

I routinely need to retrieve and package query result sets in an XML package.  The built in functions supported by SQL Server 2000 are effective, but are often more of a pain than a help.  This routine can be used in a simple select statement to XML encode the contents of a column during the select process.  The UDF is called exactly like RTRIM, LTRIM, or any other string function.

CREATE FUNCTION FN_XMLEncode (@InRawString varchar(7000))
                              RETURNS VARCHAR(8000)
AS
BEGIN
   DECLARE @StrPtr            smallint,
           @StrLength         smallint,
           @testChar          VARCHAR(1),
           @OutXMLString      VARCHAR(8000)

   SET @OutXMLString = ''
   SET @StrPtr = 1
   SET @StrLength = LEN(@InRawString)
   --loop through source and add elements to destination    WHILE @StrPtr <= @StrLength
   BEGIN
      SET @testChar = SUBSTRING(@InRawString, @StrPtr, 1)
           IF (@TestChar = '<')  SET @OutXMLString = @OutXMLString + '<'
      ELSE IF (@TestChar = '>')  SET @OutXMLString = @OutXMLString + '>'
      ELSE IF (@TestChar = '''') SET @OutXMLString = @OutXMLString + '&pos;'
      ELSE IF (@TestChar = '""') SET @OutXMLString = @OutXMLString + '"'
      ELSE IF (@TestChar = '&')  SET @OutXMLString = @OutXMLString + '&'
      ELSE SET @OutXMLString = @OutXMLString + @testChar

      SET @StrPtr = @StrPtr + 1
   END

   RETURN @OutXMLString
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating