SQL Result set to XML (with attribute in parent tags) -

table structure  id    name    dept    salary 1     john         1000 2     jamie   hr      2000 3     ron     fn      3000  xml needed follows  <data>     <emp id=1>         <name>john</name>         <dept>it</dept>         <salary>1000</salary>     </emp>     <emp id=2>         <name>jamie</name>         <dept>hr</dept>         <salary>2000</salary>     </emp>     <emp id=3>         <name>ron</name>         <dept>fn</dept>         <salary>3000</salary>     </emp> </data> 

i using following db query construct xml resultset -

select *   <dbname>  xml path('emp'), root ('data')   

not sure how above desired xml i.e attribute "id" mentioned above

try this

select id [@id]       ,name       ,dept       ,salary  yourtable xml path('emp'), root ('data') 

and carefull case sensitivity. in xml data not equal data