Format XML returned from SQL for XML

Question

Here is my Query :

   select EmployeeName, EmployeeSalary from Employee2
   for xml path('EmployeeDetails')

returns

<EmployeeDetails>
  <EmployeeName>xxxxx</EmployeeName>
  <EmployeeSalary>5000.00000</EmployeeSalary>
</EmployeeDetails>

how can i get output as

<EmployeeDetails>
   <EmployeeName = xxxxx />
   <EmployeeSalary = 5000.00000/>
</EmployeeDetails>

Answer 1

You can't, that's not valid XML

If you want something like

<EmployeeDetails Name="xxxxx" Salary="5000.00000" />

Then that can be done, but it makes little difference. XML is XML. The trick is the AUTO keyword

select EmployeeName, EmployeeSalary from Employee2
for xml AUTO

If you want precise control over XML layout use this sort of rubbish

Select
    (Select EmployeeName, EmployeeSalary From Employee2 For XML Auto, Type)
    .query(
        '<Slaves>{
            for $employee in /Employee2
            return 
                <Slave Name="{data($employee/@EmployeeName)}" Salary="{data($employee/@EmployeeSalary)}"/>
        }</Slaves>'
    )

SQL Server is not really the place for XML serialisation. Are you sure you shouldn't be doing this in another component? Typically your public interfaces (UI, API etc)

Answer 2

You should look at For Xml Explicit. For example, so using the following schema:

Create Table #Employees
(
    Id int not null identity(1,1) primary key clustered
    , EmployeeName nvarchar(100) not null
    , EmployeeSalary decimal(18,4) not null
)

Insert #Employees Values( 'ABC', 12345 )
Insert #Employees Values( 'DEF', 67890 )
Insert #Employees Values( 'GHI', 11223 )

And the following query:

Select 1 As Tag
    , Null As Parent
    , Id As [EmployeeDetails!1!Id!Hide]
    , Null As [EmployeeName!2!Name]
    , Null As [EmployeeSalary!3!Salary]
From #Employees
Union All
Select 2 As Tag
    , 1 As Parent
    , Id
    , EmployeeName
    , Null
From #Employees
Union All
Select 3 As Tag
    , 1 As Parent
    , Id
    , Null
    , EmployeeSalary
From #Employees As EmployeeDetails
Order By [EmployeeDetails!1!Id!Hide]
For Xml Explicit

I get the following which is close:

<EmployeeDetails>
    <EmployeeName Name="ABC"/>
    <EmployeeSalary Salary="12345.0000"/>
</EmployeeDetails>
<EmployeeDetails>
    <EmployeeName Name="DEF"/>
    <EmployeeSalary Salary="67890.0000"/>
</EmployeeDetails>
<EmployeeDetails>
    <EmployeeName Name="GHI"/>
    <EmployeeSalary Salary="11223.0000"/>
</EmployeeDetails>