xml file creation using datareader in c3

Question

0.00/5 (No votes)

See more:

dear all,
here is my code.
i can able to create the xml file.
but it is writing only last row value .
not writing all the rows.

public void drtoXML()
{
SqlConnection Conn = new SqlConnection("Data Source=;Initial Catalog=Employee;");
string sqlText = "select * from EMP FOR XML AUTO";
SqlCommand dataCmd = new SqlCommand(sqlText, Conn);
XmlWriter writer = null;
Conn.Open();
XmlReader dataReader = dataCmd.ExecuteXmlReader();
while (dataReader.Read())
{
using (writer = XmlWriter.Create(@"D:\Manas\Prod.xml"))
{
writer.WriteNode(dataReader, true);

}

}
dataReader.Close();
Conn.Close();

}

please help me.

Posted 27-Jun-13 20:30pm

connect2manas

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mahesh Bailwal · Answer 1 · 2013-06-27T20:34:00

Solution 1

You were creating new xml file inside loop, which was deleting old xml file
Below is updated code.

public void drtoXML()
{
SqlConnection Conn = new SqlConnection("Data Source=;Initial Catalog=Employee;");
string sqlText = "select * from EMP FOR XML AUTO";
SqlCommand dataCmd = new SqlCommand(sqlText, Conn);
XmlWriter writer = null;
Conn.Open();
XmlReader dataReader = dataCmd.ExecuteXmlReader();
using (writer = XmlWriter.Create(@"D:\Manas\Prod.xml"))
{

while (dataReader.Read())
{
writer.WriteNode(dataReader, true);
}

}
dataReader.Close();
Conn.Close();
 
}

Posted 27-Jun-13 20:34pm

Mahesh Bailwal

Comments

connect2manas 28-Jun-13 2:40am

thanks for reply.
but after editing the error is on writer.WriteNode(dataReader, true);
Error is:

Token StartElement in state EndRootElement would result in an invalid XML document. Make sure that the ConformanceLevel setting is set to ConformanceLevel.Fragment or ConformanceLevel.Auto if you want to write an XML fragment.

Mahesh Bailwal 28-Jun-13 3:14am

The problem here is that you always have to put the root element into your document. Please refer below links

http://stackoverflow.com/questions/209554/token-text-in-state-endrootelement-would-result-in-an-invalid-xml-document

http://www.dotnetperls.com/xmlwriter

connect2manas 28-Jun-13 3:12am

Hi Mahesh,
i have added the following code for the above error.
now no error.
but the result is coming alternet row.
like first row,3rd row,5th row like this.

plz help me.

string sqlText = "select EID,ENAME from EMP FOR XML AUTO";
SqlCommand dataCmd = new SqlCommand(sqlText, Conn);
XmlWriter writer = null;
Conn.Open();
XmlReader dataReader = dataCmd.ExecuteXmlReader();
XmlWriterSettings settings = new XmlWriterSettings()
{
ConformanceLevel = ConformanceLevel.Auto
};
using (writer = XmlWriter.Create(@"D:\Manas\Prodr.xml",settings))
{

while (dataReader.Read())
{
writer.WriteNode(dataReader, true);
}

}
dataReader.Close();
Conn.Close();

Mahesh Bailwal 28-Jun-13 3:20am

Try using ConformanceLevel.Fragment

connect2manas 28-Jun-13 3:32am

hi mahesh,
it is not working .same result like alternet row.

Mahesh Bailwal 28-Jun-13 3:49am

try below code

writer.WriteNode(dataReader.ReadOuterXml(), true);

http://support.microsoft.com/kb/316701

connect2manas 28-Jun-13 6:20am

no mahesh,
it is displaying error.

Mahesh Bailwal 28-Jun-13 6:27am

what error?