Creating XML file using database fields

Question

Hi all I've never dealt with XML at all. I'm having to create an xml file based on values retrieved from sql query. I have pieced together the following mostly from googling for examples. I'm getting an error when I try to open the file: The XML page cannot be displayed Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button or try again later. A name was started with an invalid character. Error processing resource 'file:///C:/Mugshot.xml'. Line 1 Position 23 ECSO11JBN013691 > Anyone know how to resolve this issue? It seems to me that it might be data related? In addition am I going about this the right way apart from the error? Here's my code: static void Main(string[] args) { XmlTextWriter textWriter = new XmlTextWriter(c:\Mugshot.xml null); textWriter.WriteStartDocument(); SqlConnection con = new SqlConnection( server =###; database =###;Initial Catalog=jail;uid=###;pwd=###); SqlCommand query = new SqlCommand(SELECT ltrim(rtrim(a.bookno)) as booknoltrim(rtrim(a.ecsoid)) as ecsoid demdatatype = CASE when a.age > 17 then 'A' else 'J' END ltrim(rtrim(a.race)) as race ltrim(rtrim(a.height)) as heightltrim(rtrim(a.lname)) as lnameltrim(rtrim(a.fname)) as fnameltrim(rtrim(a.sex)) as sexltrim(rtrim(a.dob)) as dob ltrim(rtrim(b.fsn)) as fsn FROM jail.dbo.jailbook a jail.dbo.jbcharge b where a.bookno = b.bookno and a.lname is not null); query.Connection = con; SqlDataReader reader; con.Open(); reader = query.ExecuteReader(); while (reader.Read()) { textWriter.WriteComment( ); textWriter.WriteStartElement(  + reader[bookno] +  ); textWriter.WriteStartElement(  + reader[ecsoid] +  ); textWriter.WriteStartElement(  + reader[demdatatype] +  ); textWriter.WriteStartElement(  + reader[race] +  ); textWriter.WriteStartElement(  + reader[height] +  ); textWriter.WriteStartElement(  + reader[lname] +  ); textWriter.WriteStartElement(  + reader[fname] +  ); textWriter.WriteStartElement(  + reader[sex] +  ); textWriter.WriteStartElement(  + reader[dob] +  ); textWriter.WriteStartElement(  + reader[fsn] +  ); textWriter.WriteComment( ); } textWriter.WriteEndDocument(); textWriter.Close(); con.Close(); } Thanks so much for your help.

Javeed M Shaikh · Answer

Kevin you have to pass a string to the method: Replace textWriter.WriteString(reader[bookno]); With textWriter.WriteString(reader[bookno].ToString()); Please do not forget to mark Accepted Answer.

Javeed M Shaikh · Answer

I agree with Sam his sample code can be utilized for using XMLTextWriter object.

Sam Hobbs · Answer

I am sorry I missed the need for the ToString. I really think however that my advice is more relevant to the answer. For those people in the future that need help with the use of XmlTextWriter my sample code is relevant to the use of it; the ToString is relevant to the use of SqlDataReader.

Kevin Fralick · Answer

Thanks Sam. When I do that however I get an error: the best overloaded match for system.xml.xmlwriter.writestring has some invalid arguments On the bold line... textWriter.WriteStartElement(bookingrecordidentifier); textWriter.WriteString(reader[bookno]); textWriter.WriteEndElement();

Sam Hobbs · Answer

In your XML the two characters before BookingRecordIdentifier are  A name was started with an invalid character  might be saying that that is the problem. I think XML is expecting to see a name after the first  You are certainly not using WriteStartElement correctly. So instead of: textWriter.WriteStartElement(  + reader[bookno] +  ); I think you want: textWriter.WriteStartElement(bookingrecordidentifier); textWriter.WriteString(reader[bookno]); textWriter.WriteEndElement(); I think you will have better results if you change that and the same for the other tags; PersonIdentifier and the others.

Javeed M Shaikh · Answer

did you try converting your reader to a datatable and then writexml like this: DataTable dt = new DataTable(); dt.Load( reader ); dt.WriteXml(@ c:\Mugshot.xml );