Why is '[' seen as being part of the XmlElement name?

Question

I'm trying to write a method that will query any SQLite table and return its contents as xml:

// Get any table's contents as XML; caveat: table can have at most 25 columns (this can be increased if necessary)
string IHHSDBUtils.GenericSaveDataAndReturnAsXML(string DBTableName)
{
    String xmlOutput = String.Empty;
    String qry = String.Format("SELECT * FROM {0}", DBTableName);
    try // catch
    {
        using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
        {
            conn.Open();
            XmlDocument doc = new XmlDocument();
            XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
            doc.AppendChild(dec); // Create the root element
            XmlElement root = doc.CreateElement("Command");
            doc.AppendChild(root);

            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(qry, conn))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        int colCount = rdr.FieldCount;
                        while (rdr.Read())
                        {
                            // outer val
                            XmlElement tblRec = doc.CreateElement(DBTableName);

                            if (colCount > 0)
                            {
                                String firstCol = rdr[0].ToString();
                                XmlElement _firstCol = doc.CreateElement(rdr[0].ToString()); // 
Will this return the right val?
                                _firstCol.InnerText = firstCol;
                                tblRec.AppendChild(_firstCol);
                            }

                            if (colCount > 1)
                            {
                                String secondCol = rdr[1].ToString();
                                XmlElement _secondCol = doc.CreateElement(rdr[1].ToString());
                                _secondCol.InnerText = secondCol;
                                tblRec.AppendChild(_secondCol);
                            }

                            . . .

                            if (colCount > 25)
                            {
                                String twentysixthCol = rdr[25].ToString();
                                XmlElement _twentysixthCol = 
doc.CreateElement(rdr[25].ToString());
                                _twentysixthCol.InnerText = twentysixthCol;
                                tblRec.AppendChild(_twentysixthCol);
                            }

                            root.AppendChild(tblRec);
                        } // while            
                    } // using (SQLiteDataReader
                } // using (SQLiteCommand
            }
            finally
            {
                xmlOutput = doc.OuterXml;
                doc.Save(String.Format("{0}.xml", DBTableName));
            }
        } // using (SQLiteConnection
    } //  try..catch
    catch (Exception ex)
    {
        String msgInnerExAndStackTrace = String.Format(
            "{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message, ex.InnerException, 
ex.StackTrace);
        ExceptionLoggingService.Instance.WriteLog(String.Format("From 
HHSDBUtils.GenericSaveDataAndReturnAsXML: {0}", msgInnerExAndStackTrace));
    }
    return xmlOutput;
}

I passed this method the name of a table that has fewer than 25 columns (16, to be precise), calling it (testing) like so:

MessageBox.Show(hhsdbutils.GenericSaveDataAndReturnAsXML("Inventory"));

But instead of getting back a sublimely well-formed block of xml, I got back this grating communique:

The '[' character, hexadecimal value 0x5B, cannot be included in a name.; Inner Ex: ; Stack 
Trace: at System.Xml.XmlDocument.CheckName(String name)
   at System.Xml.XmlElement..ctor(XmlName name, Boolean empty, XmlDocument doc)
   at System.Xml.XmlDocument.CreateElement(String prefix, String localName, String namespaceURI)
   at System.Xml.XmlDocument.CreateElement(String name)
   at HHS.SQLiteHHSDBUtils.HHS.IHHSDBUtils.GenericSaveDataAndReturnAsXML(String DBTableName)

I do have some placeholder vals that are encased in brackets, as can be seen from LINQPad:

Is this (having values such as "[CRVid]" in the table) the problem? If so, what other "odd" characters do I have to guard against having in column values: "{", "}", "(", ")", etc.?

UPDATE

I elegantized the code at thumbmunkey's prodding (got a munkey on my back):

string IHHSDBUtils.GenericSaveDataAndReturnAsXML(string DBTableName)
{
    String xmlOutput = String.Empty;
    String qry = String.Format("SELECT * FROM {0}", DBTableName);
    try // catch
    {
        using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
        {
            conn.Open();
            XmlDocument doc = new XmlDocument();
            XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
            doc.AppendChild(dec); // Create the root element
            XmlElement root = doc.CreateElement("Command");
            doc.AppendChild(root);

            // Note that to set the text inside the element,
            // you use .InnerText instead of .Value (which will throw an exception).
            // You use SetAttribute to set attribute
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(qry, conn))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        // outer val
                        XmlElement tblRec = doc.CreateElement(DBTableName);
                        String colName;
                        XmlElement _colName;

                        int colCount = rdr.FieldCount;

                        for (int i = 0; i < colCount; i++)
                        {
                            rdr.Read();
                            colName = rdr[i].ToString();
                            _colName = doc.CreateElement(colName); // Will this return the right 
val?
                            _colName.InnerText = colName;
                            tblRec.AppendChild(_colName);
                        }

                        root.AppendChild(tblRec);
                    } // using (SQLiteDataReader
                } // using (SQLiteCommand
            }
            finally
            {
                xmlOutput = doc.OuterXml;
                doc.Save(String.Format("{0}.xml", DBTableName));
            }
        } // using (SQLiteConnection
    } //  try..catch
    catch (Exception ex)
    {
        String msgInnerExAndStackTrace = String.Format(
            "{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message, ex.InnerException, 
ex.StackTrace);
        ExceptionLoggingService.Instance.WriteLog(String.Format("From 
HHSDBUtils.GenericSaveDataAndReturnAsXML: {0}", msgInnerExAndStackTrace));
    }
    return xmlOutput;
}

...but now, although for some reason the exception went away, the returned val does not contain the record in the table. All I see is:

<?xml version="1.0"?>
<Command>
<Inventory/>
</Command>

?

UPDATE 2

Curioser and curiouser: The problem with my Update is that I had a silly goose type (">" where it should have been "<"). But even with that fixed, I get an exception:

Date: 2/21/2009 12:54:12 AM
Message: From HHSDBUtils.GenericSaveDataAndReturnAsXML: No current row; Inner Ex: ; Stack 
Trace: at System.Data.SQLite.SQLiteDataReader.CheckValidRow()
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at System.Data.SQLite.SQLiteDataReader.get_Item(Int32 i)
   at HHS.SQLiteHHSDBUtils.HHS.IHHSDBUtils.GenericSaveDataAndReturnAsXML(String DBTableName)

...and the only xml returned is:

<?xml version="1.0"?>
<Command/>

???

UPDATE 3

I changed the code to this:

string IHHSDBUtils.GenericSaveDataAsXML(string DBTableName)
{
    String xmlOutput = String.Empty;
    String qry = String.Format("SELECT * FROM {0}", DBTableName);
    try // catch
    {
        using (SQLiteConnection conn = new SQLiteConnection(HHSUtils.GetDBConnection()))
        {
            conn.Open();
            XmlDocument doc = new XmlDocument();
            XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", null, null);
            doc.AppendChild(dec); // Create the root element
            XmlElement root = doc.CreateElement("Command");
            doc.AppendChild(root);

            // Note that to set the text inside the element,
            // you use .InnerText instead of .Value (which will throw an exception).
            // You use SetAttribute to set attribute
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand(qry, conn))
                {
                    using (SQLiteDataReader rdr = cmd.ExecuteReader())
                    {
                        //if (!rdr.HasRows) return; <= won't know this yet
                        // outer val
                        XmlElement tblRec = doc.CreateElement(DBTableName);
                        String colName;
                        XmlElement _colName;

                        while (rdr.Read())
                        {
                            int colCount = rdr.FieldCount;

                            for (int i = 0; i < colCount; i++)
                            {
                                colName = rdr[i].ToString();
                                _colName = doc.CreateElement(colName); // Will this return the right val?
                                _colName.InnerText = colName;
                                tblRec.AppendChild(_colName);
                                rdr.Read();
                            }
                            root.AppendChild(tblRec);
                        } // while (rdr.Read())
                    } // using (SQLiteDataReader
                } // using (SQLiteCommand
            }
            finally
            {
                xmlOutput = doc.OuterXml;
                doc.Save(String.Format("{0}.xml", DBTableName));
            }
        } // using (SQLiteConnection
    } //  try..catch
    catch (Exception ex)
    {
        String msgInnerExAndStackTrace = String.Format(
            "{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message, ex.InnerException, ex.StackTrace);
        ExceptionLoggingService.Instance.WriteLog(String.Format("From HHSDBUtils.GenericSaveDataAndReturnAsXML: {0}", msgInnerExAndStackTrace));
    }
    return xmlOutput;
}

...but still get the same err msg and xml result as detailed in Upate 2.

Answer 1

How about this:

    static string GenerateXmlFromTable(string tableName, bool useAttribute)
    {
        XmlDocument document = new XmlDocument();
        XmlDeclaration declaration = document.CreateXmlDeclaration("1.0", null, null);
        document.AppendChild(declaration);

        XmlElement rootElement = document.CreateElement("Command");
        document.AppendChild(rootElement);

        using (SQLiteConnection connection = new SQLiteConnection("Data source=SqlLite.db"))
        {
            connection.Open();

            string query = string.Format("Select * from {0}", tableName);

            using(SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                using(SQLiteDataReader reader = command.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        XmlElement recordElement = document.CreateElement(tableName);
                        rootElement.AppendChild(recordElement);

                        for(int index = 0; index < reader.FieldCount; index++)
                        {
                            if (useAttribute)
                            {
                                recordElement.SetAttribute(reader.GetName(index), reader.GetValue(index).ToString());
                            }
                            else
                            {
                                XmlElement valueElement = document.CreateElement(reader.GetName(index));
                                valueElement.InnerText = reader.GetValue(index).ToString();

                                recordElement.AppendChild(valueElement);
                            }
                        }
                    }
                }
            }
        }

        using(StringWriter stringWriter = new StringWriter(new StringBuilder()))
        using(XmlTextWriter xmlWriter = new XmlTextWriter(stringWriter))
        {
            xmlWriter.Formatting = Formatting.Indented;

            document.Save(xmlWriter);

            return stringWriter.ToString();
        }
    }