Creating XML file using database fields

Kevin Fralick

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 bookno,ltrim(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 height,ltrim(rtrim(a.lname)) as lname,ltrim(rtrim(a.fname)) as fname,ltrim(rtrim(a.sex)) as sex,ltrim(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.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Javeed M Shaikh15y ago
Accepted 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".
0
Javeed M Shaikh15y ago
I agree with Sam, his sample code can be utilized for using XMLTextWriter object.
0
Sam Hobbs15y ago
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.
0
Kevin Fralick15y ago
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();
0
Sam Hobbs15y ago
In your XML the two characters before "BookingRecordIdentifier" are "<<" (two less-than characters). If that truly is what your data is, then that is a problem. I think the message "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 "<" and the second "<" would be invalid.

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.
0
Javeed M Shaikh15y ago
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");
0