The following code shows an example on how to avoid T (space) while generating XML using FOR XML clause

Sample Data:

DECLARE @Employee TABLE
(ID INT,
Name VARCHAR(100),
DOJ DATETIME)
  
INSERT @Employee SELECT 1,'Sathya','2013-06-08 08:50:52.687'
INSERT @Employee SELECT 2,'Madhu K Nair','2008-06-08 08:50:52.687'
INSERT @Employee SELECT 3,'Vidhyasagar','2008-06-08 08:50:52.687'
  
SELECT * FROM @Employee


--you will find T(space),if you are not converting date column with proper datetime style,
SELECT * FROM  @Employee 
FOR XML PATH('Employee')

Output XML for above query :

<Employee>
  <ID>1</ID>
  <Name>Sathya</Name>
  <DOJ>2013-06-08T08:50:52.687</DOJ>
</Employee>
<Employee>
  <ID>2</ID>
  <Name>Madhu K Nair</Name>
  <DOJ>2008-06-08T08:50:52.687</DOJ>
</Employee>
<Employee>
  <ID>3</ID>
  <Name>Vidhyasagar</Name>
  <DOJ>2008-06-08T08:50:52.687</DOJ>
</Employee>

--converting date column with proper datetime style (120/121)
SELECT ID,
       Name,
       CONVERT(VARCHAR(25),DOJ,121) DOJ 
FROM @Employee 
FOR XML PATH('Employee')

Output XML for above query :

<Employee> 
  <ID>1</ID> 
  <Name>Sathya</Name
  <DOJ>2013-06-08 08:50:52.687</DOJ> 
</Employee> 
<Employee> 
  <ID>2</ID> 
  <Name>Madhu K Nair</Name
  <DOJ>2008-06-08 08:50:52.687</DOJ> 
</Employee> 
<Employee> 
  <ID>3</ID> 
  <Name>Vidhyasagar</Name
  <DOJ>2008-06-08 08:50:52.687</DOJ> 
</Employee>



See also :

  • My articles on SQL Server XML