asp.net - How to create XML file by SQL query? -

i have select query selects top 10 values table

create proc [dbo].[sp_select_top10]    select distinct top (10)        score, fbid             fb_player     order         score desc 

what need have results of query in xml file as.

<player>     <name> </name>     <score> </score> </player> 

i use asp.net create file how can ?

create stored procedure - use for xml path(), root() syntax have sql server generate proper xml you:

create procedure dbo.procgetplayerscore begin     select distinct top (10)         id '@id',   -- creates attribute on <player> node         name,          -- gets output element inside <player>         score          -- gets output element inside <player>              dbo.fb_players     order          score desc      xml path('player'), root('allplayers') end 

in c# code, need - connect database, execute stored procedure, single row, single column of stored procedure (the xml produced):

// set sql server connection , command execute stored procedure using(sqlconnection conn = new sqlconnection("server=.;database=test;integrated security=sspi")) using (sqlcommand cmdgetplayers = new sqlcommand("dbo.procgetplayerscore", conn))  {      // define it's stored procedure      cmdgetplayers.commandtype = commandtype.storedprocedure;       // open connection, execute procedure, resulting xml, close connection      conn.open();      string playersxml = cmdgetplayers.executescalar().tostring();      conn.close(); } 

as result, you'll xml this:

<allplayers>     <player id="4">         <name>player 4</name>         <score>72.1500</score>     </player>     <player id="1">         <name>player 1</name>         <score>50.5000</score>     </player>     ...... </allplayers>