How to create XML file by SQL query?

Question

I have a Select query which selects the top 10 values from table

Create proc [dbo].[SP_Select_Top10]
as
   select DISTINCT top (10)
       Score, FBid 
   from 
       FB_Player 
   ORDER BY 
       Score DESC

What I need is to have the results of this query in xml file as.

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

I use ASP.NET to create this file how can I do that ?

Answer 1

Create your stored procedure like this - use the FOR XML PATH(), ROOT() syntax to have SQL Server generate a proper XML for you:

CREATE PROCEDURE dbo.procGetPlayerScore
AS BEGIN
    SELECT DISTINCT TOP (10)
        ID AS '@ID',   -- creates an attribute on the <Player> node
        Name,          -- gets output as element inside <Player>
        Score          -- gets output as element inside <Player>
    FROM 
        dbo.FB_Players
    ORDER BY 
        Score DESC 
    FOR XML PATH('Player'), ROOT('AllPlayers')
END

In your C# code, you need something like this - connect to the database, execute the stored procedure, get back the single row, single column of that stored procedure (the XML produced):

// set up SQL Server connection and command to execute the stored procedure
using(SqlConnection conn = new SqlConnection("server=.;database=test;Integrated Security=SSPI"))
using (SqlCommand cmdGetPlayers = new SqlCommand("dbo.procGetPlayerScore", conn)) 
{
     // define that it's a stored procedure
     cmdGetPlayers.CommandType = CommandType.StoredProcedure;

     // open connection, execute procedure, get resulting XML, close connection
     conn.Open();
     string playersXml = cmdGetPlayers.ExecuteScalar().ToString();
     conn.Close();
}

As result, you'll get a XML something like 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>

Answer 2

I would suggest looking at the native XML options of SQL Server

Link here

Also please note

<player>
<playername="" score="" />
</player>

is not valid xml, it would have to something like

<player name="" score="" />

or

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

depending on whether you want to be element-centric or attribute-centric, but all of these can be specified in the SQL server XML output options. Then you could just get the ASP.NET side to save the resulting query as a file.