Edit

FOR XML support for string data types

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

The XML generated by the FOR XML white space characters in the data is entitized.

The following example creates a sample table T and inserts sample data that includes the line feed, carriage return, and tab characters. The SELECT statement retrieves the data from the table.

CREATE TABLE T
(
  c1 int identity primary key,
  c2 varchar(100)
);
GO

INSERT T (c2) VALUES ('Special character 0xD for carriage return ' + convert(varchar(10), 0xD) + ' after carriage return');
INSERT T (c2) VALUES ('Special character 0x9 for tab ' + convert(varchar(10), 0x9) + ' after tab' );
INSERT T (c2) VALUES ('Special character 0xA for line feed ' + convert(varchar(10), 0xA) + ' after line feed');
GO
SELECT *
FROM T
FOR XML AUTO;
GO

This is the result:

<T c1="1" c2="Special character 0xD for carriage return
after carriage return" />
<T c1="2" c2="Special character 0x9 for tab     after tab" />
<T c1="3" c2="Special character 0xA for line feed
after line feed" />

Note the following from the previous query:

  • The carriage return in the first row is entitized as &#xD.

  • The tab character in the second row is entitized as &#x09.

  • The line feed character in the third row is entitized as &#xA.

See also