Create XML file using database fields

[fralo]

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

<?xml version="1.0"?><<BookingRecordIdentifier>ECSO11JBN013691</BookingRecordIdentifier>><<Per...

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("<AddBookingRecord>");
textWriter.WriteStartElement("<BookingRecordIdentifier>" + reader["bookno"] + "</BookingRecordIdentifier>");
textWriter.WriteStartElement("<PersonIdentifier>" + reader["ecsoid"] + "</PersonIdentifier>");
textWriter.WriteStartElement("<Demdatatype>" + reader["demdatatype"] + "</Demdatatype>");
textWriter.WriteStartElement("<Race>" + reader["race"] + "</Race>");
textWriter.WriteStartElement("<Height>" + reader["height"] + "</Height>");
textWriter.WriteStartElement("<Lastname>" + reader["lname"] + "</Lastname>");
textWriter.WriteStartElement("<Firstname>" + reader["fname"] + "</Firstname>");
textWriter.WriteStartElement("<Gender>" + reader["sex"] + "</Gender>");
textWriter.WriteStartElement("<Dob>" + reader["dob"] + "</Dob>");
textWriter.WriteStartElement("<Charge>" + reader["fsn"] + "</Charge>");

//bookingidentifier(reader["ltrim(rtrim(a.bookno))"]);
//personidentifier(reader["ltrim(rtrim(a.ecsoid))"]);
//demdatatype(reader["ltrim(rtrim(a.demdatatype))"]);
//race(reader["ltrim(rtrim(a.race))"]);
//height(reader["ltrim(rtrim(a.height))"]);
//lastname(reader["ltrim(rtrim(a.lname))"]);
//firstname(reader["ltrim(rtrim(a.fname))"]);
//gender(reader["ltrim(rtrim(a.sex))"]);
//dob(reader["ltrim(rtrim(a.dob))"]);
//picture();
//charge(reader["ltrim(rtrim(a.fsn))"]);

textWriter.WriteComment("</AddBookingRecord>");

}
textWriter.WriteEndDocument();
textWriter.Close();
con.Close();

}

Thanks so much for your help.

 

[Diancecht]

Two points:

- You need a tag that encloses the whole document
- Looks like WriteStartElement adds < and > for you

Cheers,
Dian