How to write query to generate XML file from SQL Server table

Question

Hi please tell me how to generate XML file from Table in SQL Server using Query. My Query is,

 select JobTitle as title,PostedDate as date from Jobs

C# code,

 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;
        }

My stored procedure is,

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

It is giving output as follows,

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

Here i want to add Root node as jobs and sub root node as job. I tried select JobTitle as title,PostedDate as date from Jobs FOR XML PATH('jobs') it is not working. I want output as follows,

<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>

Thank you ..

Abhishek · Accepted Answer · 2014-10-09 06:48:14Z

1

Try this one

    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')

edited Oct 9, 2014 at 6:48

answered Oct 9, 2014 at 6:14

Abhishek

19611 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Srinivas Over a year ago

Thank you @Abhishek, i tried your code..It is giving output like this.. - <NewDataSet> - <Table> <XML_F52E2B61-18A1-11d1-B105-00805F49916B><jobs><job>....></jobs></XML_F52E2B61-18A1-11d1-B105-00805F49916B> </Table> </NewDataSet>

Abhishek Over a year ago

Hi 1612, Its quite difficult to generate the whole code, my approach in this case will be: from stored procedure I will return the 'xml' and on c# code I will opt for string rather than dataset. And will simply apply linq to get <Jobs> element before writing to xml file. LinQ like 'var result = xmldoc.Element("Jobs"))'......... will do the job elimanating elements like '</XML_F52E2B61-‌18A1-11d1-B105-00805F49916B> </Table> </NewDataSet>'.

Srinivas Over a year ago

Thank you @Abhishek , If i remove ' FOR XML PATH('Job'), ROOT('JOBS') ' from query its working. But i am getting <Table> Node.

mybirthname · Accepted Answer · 2014-10-09 06:17:07Z

0

        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();

Here is example what you need to do. In your case when you fetch the data from database in method GetJobDetails, you should return DataSet, with DataSetName = "Jobs" and the defaultTableName of the DataTable should be Job !

You are not showing the method which fetch the data from database because of the I write you this example !

Edit:

First in btnXML_Click write it like this:

DataSet dsJobsDetails = GetJobDetails();

instead of

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

In private DataSet GetJobDetails() write it like this:

DataSet ds = new DataSet("Jobs");

instead of:

DataSet ds = null;

IN SQLProvider class you should add property TableName and when DataSqlAdapter.Fill the query like this:

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

You need to set the property TableName value to Job

edited Oct 9, 2014 at 6:17

answered Oct 9, 2014 at 6:00

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

2 Comments

mybirthname Over a year ago

@1612 I hope you are using SqlDataAdapter.Fill method to return dataSet ?

mybirthname Over a year ago

@1612 I can not be more specific hope it helps you.

Collectives™ on Stack Overflow

How to write query to generate XML file from SQL Server table

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related