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

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

From XML Elements at W3Schools:

XML elements must follow these naming rules:

• Names can contain letters, numbers, and other characters
• Names cannot start with a number or punctuation character
• Names cannot start with the letters xml (or XML, or Xml, etc)
• Names cannot contain spaces

Any name can be used, no words are reserved.

(Emphasis added)

I guess this is the same question? FOR XML AUTO and column name with space problem in SQL Server 2005

The answer is the same. You can not have a space in a XML tag name.

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>