Field name with space problem in case of for xml auto output

Question

my sql is very simple

select ID as [Employee ID], EmpName as [Employee Name], Sal as [Salary] from Emp FOR XML AUTO, ELEMENTS, ROOT('customers')

when i execute this sql then i am getting output in xml format. the xml output is

<customers>
<Emp>
  <Employee_x0020_ID>1</Employee_x0020_ID>
  <Employee_x0020_Name>Tridip</Employee_x0020_Name>
  <Salary>2500</Salary>
</Emp>
<Emp>
  <Employee_x0020_ID>2</Employee_x0020_ID>
  <Employee_x0020_Name>Ari</Employee_x0020_Name>
  <Salary>4000</Salary>
</Emp>
<Emp>
  <Employee_x0020_ID>3</Employee_x0020_ID>
  <Employee_x0020_Name>Dibyendu</Employee_x0020_Name>
  <Salary>3500</Salary>
</Emp>
</customers>

if u see the xml filed name then u can understand that field name is generated dynamically due to space. <Employee_x0020_ID>1</Employee_x0020_ID> this is dynamically generated but i want it should be generated like <Employee ID>1</Employee ID>. i want space should be maintain in the xml filed name. so please tell me what to do........thanks

Answer 1

Here is the output you are looking for that pretends to be XML. You won't even be able to cast it to XML in any parser (at least it shouldn't - it won't in SQL Server).

;with emp(ID, EmpName, Sal) as (select
    1, 'tridip', 2500 union all select
    2, 'ari', 4000)

select replace(convert(varchar(max),(
    select ID as [Employee ID], EmpName as [Employee Name], Sal as [Salary] 
    from Emp 
    FOR XML AUTO, ELEMENTS, ROOT('customers'))),
    '_x0020_', ' ')

Output (reformatted for clarity - SQL Server returns it all on one line)

<customers><Emp><Employee ID>1</Employee ID><Employee Name>tridip
</Employee Name><Salary>2500</Salary></Emp><Emp>
<Employee ID>2</Employee ID><Employee Name>ari</Employee Name>
<Salary>4000</Salary></Emp></customers>