FOR XML AUTO and column name with space problem in SQL Server 2005
-
Here is my SQL select ID as [Emp ID], Name as [Employee name], Sal as [Salary] from Emp FOR XML AUTO, ELEMENTS, ROOT('customers') When I issue this SQL then unknown data is added in xml with my field name. I got the xml like <customers> <Emp> <Emp_x0020_ID>1</Emp_x0020_ID> <Employee_x0020_name>bob</Employee_x0020_name> <Salary>2020</Salary> </Emp> <Emp> <Emp_x0020_ID>2</Emp_x0020_ID> <Employee_x0020_name>keith</Employee_x0020_name> <Salary>6500</Salary> </Emp> <Emp> <Emp_x0020_ID>3</Emp_x0020_ID> <Employee_x0020_name>markc</Employee_x0020_name> <Salary>5400</Salary> </Emp> </customers> It shows <Emp_x0020_ID> instead of this <Emp ID> because I mention column alias with space like "Emp ID". how could modify my SQL as a result space will not be ignore I mean my xml would <customers> <Emp> <Emp ID>1</Emp ID> <Employee name>bob</Employee name> <Salary>2020</Salary> </Emp> </customers> please help me......urgent. look like
tbhattacharjee
-
Here is my SQL select ID as [Emp ID], Name as [Employee name], Sal as [Salary] from Emp FOR XML AUTO, ELEMENTS, ROOT('customers') When I issue this SQL then unknown data is added in xml with my field name. I got the xml like <customers> <Emp> <Emp_x0020_ID>1</Emp_x0020_ID> <Employee_x0020_name>bob</Employee_x0020_name> <Salary>2020</Salary> </Emp> <Emp> <Emp_x0020_ID>2</Emp_x0020_ID> <Employee_x0020_name>keith</Employee_x0020_name> <Salary>6500</Salary> </Emp> <Emp> <Emp_x0020_ID>3</Emp_x0020_ID> <Employee_x0020_name>markc</Employee_x0020_name> <Salary>5400</Salary> </Emp> </customers> It shows <Emp_x0020_ID> instead of this <Emp ID> because I mention column alias with space like "Emp ID". how could modify my SQL as a result space will not be ignore I mean my xml would <customers> <Emp> <Emp ID>1</Emp ID> <Employee name>bob</Employee name> <Salary>2020</Salary> </Emp> </customers> please help me......urgent. look like
tbhattacharjee
Tridip Bhattacharjee wrote:
please help me......urgent.
I don't see anything urgent here. However, if you read the XML specification you may notice that node names may not contain spaces, so your
Emp IDlabel has been manipulated to make it comply with the rules.I must get a clever new signature for 2011.