Share via

  • Article

FOR XML Support for String Data Types

In SQL Server 2000, white space characters in data were returned exactly as they are. Beginning with SQL Server 2005, 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 &#xD; after carriage return" />
  <T c1="2" c2="Special character 0x9 for tab &#x09; after tab" />
  <T c1="3" c2="Special character 0xA for line feed &#xA; 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.

In SQL Server 2000, you would receive the following result, because the white spaces are not entitized:

<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"/>