如何编写查询以从SQL Server表生成XML文件

嗨，请告诉我如何使用Query从SQL Server中的表生成XML文件。我的查询是，

 select JobTitle as title,PostedDate as date from Jobs 

C#代码，

 protected void btnXML_Click(object sender, EventArgs e)
        {
            try
            {
                DataSet dsJobsDetails = new DataSet();
                dsJobsDetails = GetJobDetails();
                string fileLoc = @"C:'JobDocuments'jobsxml.xml";
                FileStream fs = null;
                fs = File.Create(fileLoc);
                fs.Close();
                if (File.Exists(fileLoc))
                {
                    dsJobsDetails.WriteXml(fileLoc);
                }
            }
            catch { }
        }

private DataSet GetJobDetails()
        {
            DataSet ds = null;
            SQLProvider provider = new SQLProvider();
            SqlParameter[] paramCandidate = new SqlParameter[2];
            try
            {
                paramCandidate[0] = provider.MakeParameter("JobID", SqlDbType.VarChar, "0");
                paramCandidate[1] = provider.MakeParameter("AccountID", SqlDbType.Int, "0");
                ds = provider.RunProcedure("Usp_GetJobsDetailsForXML", paramCandidate);
            }
            catch (Exception ex)
            {
                SetLastError(ex);
                paramCandidate = null;
                provider = null;
                ds = null;
            }
            finally
            {
                paramCandidate = null;
                provider = null;
            }
            return ds;
        }

我的存储过程是，

CREATE PROCEDURE [dbo].[Usp_GetJobsDetailsForXML]          
(                                   
    @JobID varchar(8000),  
    @AccountID int              
)                                  
AS                              
BEGIN                                                                   
    SET NOCOUNT ON;                                        
     select JobTitle as title,PostedDate as date,JobDescription as [description] from Jobs 
     where JobID in (@JobID)   
     --FOR XML PATH('Jobs')                                                   
END

它给出的输出如下，

 <NewDataSet>
    <Table>
      <title>DBA</title> 
      <date>2014-09-30</date> 
      </Table>
     <Table>
      <title>Manager</title> 
      <date>2014-09-30</date>
     </Table>
    </NewDataSet>

在这里，我想添加根节点作为作业，子根节点作为工作。我试过了select JobTitle as title,PostedDate as date from Jobs FOR XML PATH('jobs')它不工作。我希望输出如下，

<jobs>
<job>
<title>
<![CDATA[ DBA ]]>
</title>
<date>
<![CDATA[ 2014-09-30 ]]>
</date>
</job>
<job>
<title>
<![CDATA[ Manager ]]>
</title>
<date>
<![CDATA[ 2014-09-30 ]]>
</date>
</job>
<jobs>

谢谢。。

    SELECT '<![CDATA[' + JobTitle + ']]>' as title,
       '<![CDATA[' + PostedDate + ']]>'  as date,
       '<![CDATA[' + JobDescription + ']]>' as [description] 
FROM Jobs 
     WHERE JobID in (@JobID)   
     FOR XML PATH('Job'), ROOT('JOBS')

        DataSet dst = new DataSet("Jobs");
        DataTable table = new DataTable("Job");
        DataColumn c1 = new DataColumn("Title");
        DataColumn c2 = new DataColumn("Date");
        table.Columns.Add(c1);
        table.Columns.Add(c2);
        DataRow row1 = table.NewRow();
        row1["Title"] = "T1";
        row1["Date"] = "D1";
        DataRow row2 = table.NewRow();
        row2["Title"] = "T2";
        row2["Date"] = "D2";
        table.Rows.Add(row1);
        table.Rows.Add(row2);
        dst.Tables.Add(table);
        string p = dst.GetXml();

DataSet dsJobsDetails = GetJobDetails();

DataSet dsJobsDetails = new DataSet();
dsJobsDetails = GetJobDetails();

DataSet ds = null;

 using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
 {
     adapter.Fill(resultDst, TableName);
 }