How to format XML in SQL Server

Question

I am extracting some data from SQL Server. One of the columns is a ntext column that has a blob of XML there. As I extract it, it looks really ugly. Is there any way to format the XML inside a SQL Server 2005 stored proc?

Answer 1

Had same issue, just wrote my own function:

CREATE FUNCTION LocalCustom.Pretty_XML(@uglyXML VARCHAR(MAX))
RETURNS VARCHAR(MAX) 
AS
BEGIN
  DECLARE @len AS INT = LEN(@uglyXML)
        , @prettyXML AS VARCHAR(MAX) = ''
        , @indent AS INT = 0
        , @tab AS CHAR(1) = CHAR(9)
        , @lf AS CHAR(1) = CHAR(10)
        , @currElement AS VARCHAR(MAX) = NULL
        , @nextElement AS VARCHAR(MAX);

  WHILE @uglyXML + ISNULL(@currElement,'') <> ''
  BEGIN
     IF @currElement IS NULL
     BEGIN
        SET @currElement = LocalCustom.GetColumn(1,'>',@uglyXML);
        SET @uglyXML = SUBSTRING(@uglyXML,LEN(@currElement)+2,@len);
     END;
     SET @nextElement = LocalCustom.GetColumn(1,'>',@uglyXML);
     IF @currElement LIKE '</%'
     BEGIN
        SET @indent = @indent - 1;
        SET @prettyXML = @prettyXML + REPLICATE(@tab,@indent) +  @currElement + '>' + CASE @indent WHEN 0 THEN '' ELSE @lf END;
        SET @currElement = @nextElement;
        SET @uglyXML = SUBSTRING(@uglyXML,LEN(@nextElement)+2,@len);
     END
     ELSE
        IF @currElement LIKE '<% /'
        BEGIN
           SET @prettyXML = @prettyXML + REPLICATE(@tab,@indent) +  @currElement + '>' + CASE @indent WHEN 0 THEN '' ELSE @lf END;
           SET @currElement = @nextElement;
           SET @uglyXML = SUBSTRING(@uglyXML,LEN(@nextElement)+2,@len);
        END
        ELSE
           IF @nextElement LIKE '%</' + SUBSTRING(@currElement,2,@len) 
           BEGIN
              SET @prettyXML = @prettyXML + REPLICATE(@tab,@indent) +  @currElement + '>' + @nextElement + '>' + @lf;
              SET @uglyXML = SUBSTRING(@uglyXML,LEN(@nextElement)+2,@len);
              SET @currElement = NULL;
           END
           ELSE
              IF @nextElement NOT LIKE '</%'
              BEGIN
                 SET @prettyXML = @prettyXML + REPLICATE(@tab,@indent) +  @currElement + '>' + @lf;
                 SET @indent = @indent + 1;
                 SET @currElement = @nextElement;
                 SET @uglyXML = SUBSTRING(@uglyXML,LEN(@nextElement)+2,@len);
              END;
  END;
  RETURN @prettyXML;
END
GO

The above function uses an existing one we have here:

CREATE FUNCTION [LocalCustom].[GetColumn]
(@ColumnNumber INT,
 @Delimiter VARCHAR(32),
 @Record VARCHAR(2000)) 
RETURNS VARCHAR(500)
AS
BEGIN
  DECLARE @ReturnValue VARCHAR(500)
  DECLARE @StartPosition INT
  DECLARE @EndPosition INT
  DECLARE @Counter INT
  -- Find position of first opening delimeter
  IF @ColumnNumber = 1
      SET @StartPosition = 1 - DATALENGTH(@Delimiter)
  ELSE
      BEGIN
      SET @StartPosition = 1
      SET @Counter = 1
      WHILE @StartPosition < LEN(@Record) AND @StartPosition <> 0 AND @Counter < @ColumnNumber 
          BEGIN
          SET @StartPosition = CHARINDEX(@Delimiter,@Record,@StartPosition+1)
          SET @Counter = @Counter + 1
          END
      IF @StartPosition = 0
          SET @StartPosition = LEN(@Record) + 1
      END
  -- Find position of closing delimeter
  SET @EndPosition = CHARINDEX(@Delimiter,@Record,@StartPosition+1)
  IF @EndPosition = 0
      SET @EndPosition = LEN(@Record) + 1
  -- If Valid start and end positions get column
  IF @EndPosition > @StartPosition AND @EndPosition <= LEN(@Record) + 1
      IF @EndPosition - @StartPosition - 1 = 0
          SET @ReturnValue = ''
      ELSE
          SET @ReturnValue = SUBSTRING(@Record, @StartPosition + DATALENGTH(@Delimiter), @EndPosition - @StartPosition - DATALENGTH(@Delimiter))
  ELSE
      SET @ReturnValue = NULL
  -- Strip Carriage Return, Line Feed character
  IF @ReturnValue IS NOT NULL
      BEGIN
      SET @ReturnValue = REPLACE(@ReturnValue,CHAR(10),'')
      SET @ReturnValue = REPLACE(@ReturnValue,CHAR(13),'')
      END
  -- Remove double quotes if used as column delimiter
  IF LEFT(@ReturnValue,1) = '"' AND RIGHT(@ReturnValue,1) = '"'
     BEGIN
     SET @ReturnValue = SUBSTRING(@ReturnValue,2,LEN(@ReturnValue)-2)
     SET @ReturnValue = REPLACE(@ReturnValue,'""','"')
     END
  RETURN @ReturnValue
END
GO

Answer 2

There is no "make this pretty" function in SQL Server. You can do it in .NET, however, so one option is setting up a CLR function that makes the output of XML pretty. As this is NText, you will have to exception handle any string that is not XML, or you will have a blow up.

Answer 3

Not easily, but if you INSERT it with formatting, then sqlserver will keep the formatting when you extract it. Doing text formatting in SQL Server is pretty difficult as the functions available are very limited.