asp.net - How to select query as XML format? -

my query selects fields in xml. need display xml not in 1 line as

<allplayers><player><fbid>1236598</fbid><fbname>joan</fbname><fbscore>99999999</fbscore></player><player><fbid>55559999888</fbid><fbname>smith</fbname><fbscore>99999999</fbscore></player></allplayers> 

but :

<allplayers>   <player>     <fbid>1236598</fbid>     <fbname>mohamed hamam</fbname>     <fbscore>99999999</fbscore>   </player>   <player>     <fbid>55559999888</fbid>     <fbname>mostafaa hamam</fbname>     <fbscore>99999999</fbscore>   </player> </allplayers> 

my query:

alter procedure [dbo].[procgetplayerscore] begin     select distinct top (10)         fbid ,            name fbname,         score  fbscore                      dbo.fb_player     order          score desc      xml  path('player'), root('allplayers') end 

asp page:

response.expires = -1; response.contenttype = "text/xml";  using(system.data.sqlclient.sqlconnection c = new sqlconnection(configurationmanager.connectionstrings["x"].connectionstring)) using (system.data.sqlclient.sqlcommand cmd = c.createcommand()) {     cmd.commandtext = "procgetplayerscore";     cmd.commandtype = commandtype.storedprocedure;      c.open();     system.xml.xmlreader r = cmd.executexmlreader();     string playersxml = cmd.executescalar().tostring();     xmlc.innertext = playersxml;     system.xml.xmltextwriter w = new system.xml.xmltextwriter(response.output);      c.close(); } 

you need specify formatting in xmltextwriter bellow: (i assume xmlc xmldocument)

[your code] system.xml.xmltextwriter w = new system.xml.xmltextwriter(response.output); w.formatting = formatting.indented; xmlc.writecontentto(w); c.close(); } 

update

below updated code based on comments:

[your code] string playersxml = cmd.executescalar().tostring(); system.xml.xmltextwriter w = new system.xml.xmltextwriter(response.output); w.formatting = formatting.indented; xmldocument doc = new xmldocument(); doc.loadxml(xml);  using (var stringwriter = new stringwriter())  using (var xmltextwriter = xmlwriter.create(stringwriter))  {    xmldoc.writeto(xmltextwriter);    xmltextwriter.flush();    playersxml  = stringwriter.getstringbuilder().tostring();  }  xmlc.innertext = playersxml; c.close(); }