在 for xml 自动输出的情况下有空格问题的字段名称 [英] Field name with space problem in case of for xml auto output

问题描述

我的sql很简单

从 Emp FOR XML AUTO, ELEMENTS, ROOT('customers') 中选择 ID 作为 [Employee ID]，EmpName 作为 [Employee Name]，Sal 作为 [Salary]

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

当我执行这个 sql 时，我得到了 xml 格式的输出.xml输出是

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>

如果你看到 xml 文件名，那么你可以理解该字段名是动态生成的由于空间.<Employee_x0020_ID>1</Employee_x0020_ID> 这是动态生成的，但我希望它应该像 <Employee ID>1</Employee ID> 一样生成.我希望在 xml 文件名中保留空间.所以请告诉我该怎么做........谢谢

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

推荐答案

这是您要查找的假装为 XML 的输出.您甚至无法在任何解析器中将其转换为 XML(至少它不应该 - 它不会在 SQL Server 中).

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_', ' ')

输出(为清晰起见重新格式化 - SQL Server 在一行中返回所有内容)

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>

这篇关于在 for xml 自动输出的情况下有空格问题的字段名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！