sql xml with nested node

I have a below table in MS sql server 2012

 emp_ID     emp_dept
 -2          HR
  3          CS

I need the below xml format :

<row>
  <staticData>
  <Field fld="emp_ID" value="-2" />
  <Field fld="emp_ID" value="3" /> 
  </staticData> 
 <DynData>
  <Field fld="emp_dept" value="HR" />
  <Field fld="emp_dept" value="CS" />
  </DynData> 
</row>

I have the sample query but it is not working for multiple columns :

  SELECT top 5 
 'emp_ID'  AS [Field/@fld],  
 emp_ID  AS [Field/@value]   
 FROM Employee
 FOR XML PATH ('staticData'), ROOT('row')

Answer

SQL XML generation is a bit weird. If you need to have two elements of the same name following each other you need to separate them with a null valued column, e.g.:

select top 5 
  'emp_ID' as [Field/@fld],  
  emp_ID as [Field/@value],
  null, --<<-- TRICK
  'emp_dept' as [Field/@fld],  
  emp_dept as [Field/@value]
from (values
  (-2, N'HR'),
  (3, N'CS')
) Employee (emp_ID, emp_dept)
for xml path('staticData'), root('row')

Which yields…

<row>
    <staticData>
        <Field fld="emp_ID" value="-2"/>
        <Field fld="emp_dept" value="HR"/>
    </staticData>
    <staticData>
        <Field fld="emp_ID" value="3"/>
        <Field fld="emp_dept" value="CS"/>
    </staticData>
</row> 

=====

Second answer to match the second version of the question…

I’d be querying your designer about the requirements of the new XML. There’s now no relationship between the elements containing emp_ID and emp_dept attributes. Previously they were siblings grouped together inside the staticData parent elements, now they are in separate parents and can be ordered independently according to the whims of SQL Server.

To generate your new XML format requires subqueries to populate the children of the staticData and DynData elements, e.g.:

create table dbo.Employee (
  emp_ID int not null,
  emp_dept nvarchar(2) not null
);

insert dbo.Employee (emp_ID, emp_dept) values
  (-2, N'HR'),
  (3, N'CS');

select
  (
    select
      'emp_ID' as [@fld],  
      emp_ID as [@value]
    from Employee
    for xml path('Field'), type
  ) as [staticData],
  (
    select
      'emp_dept' as [@fld],  
      emp_dept as [@value]
    from Employee
    for xml path('Field'), type
  ) as [DynData]
for xml path('row');

Which yields…

<row>
    <staticData>
        <Field fld="emp_ID" value="-2"/>
        <Field fld="emp_ID" value="3"/>
    </staticData>
    <DynData>
        <Field fld="emp_dept" value="HR"/>
        <Field fld="emp_dept" value="CS"/>
    </DynData>
</row>