Read XML into a SqlDataReader object

I'm trying to read an xml file into a SqlDataReader.

I have the following xml file:

xml version="1.0" encoding="utf-8" ?>
    <Table>
        <ID >1ID>
        <Name >JoeName>
         >123 Main StreetStreet1>
        <Street2 >Apt #1Street2>
        <City >CityCity>
        <County >CountyCounty>
        <State >StateState>
        <Zipcode >ZipZipcode>
        <Province >ProvinceProvince>
        <Country >CountryCountry>
        <Phone >1234567890Phone>
        <Fax >1234567890Fax>
    Table>

I have the following xsd file:

xml version="1.0"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Table">
        <xs:complexType>
        <xs:sequence>
            <xs:element name="ID" type="xs:int" />
            <xs:element name="Name" type="xs:string" />
            <xs:element name="Street1" type="xs:string" />
            <xs:element name="Street2" type="xs:string" />
            <xs:element name="City" type="xs:string" />
            <xs:element name="County" type="xs:string" />
            <xs:element name="State" type="xs:string" />
            <xs:element name="Zipcode" type="xs:string" />
            <xs:element name="Province" type="xs:string" />
            <xs:element name="Country" type="xs:string" />
            <xs:element name="Phone" type="xs:unsignedInt" />
            <xs:element name="Fax" type="xs:unsignedInt" />
        xs:sequence>
        xs:complexType>
        xs:element>
xs:schema>

(You can assume the xml, and xsd files are correct, hopefuly I didn't typo when I c/p).
I've written the following C# code to read in the xml into a SQLDataReader. I'd like the column names to corrospond to the name of the nodes(for example "ID", "Name", "Street1" etc...).

Code Sample #1

DataTable table = new DataTable("Table");
SqlDataAdapter adaptor = new SqlDataAdapter();
SqlDataReader reader = null;
string schema = "RelativePath/to/Schema.xsd";
string xml = "RelativePath/to/File.xml";

table.ReadXmlSchema(schema);
table.ReadXml(xml);

adaptor.Fill(table);
table.Load(reader);

I'm getting the following error:
System.InvalidOperationException: The SelectCommand property has not been initialized before calling 'Fill'.
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

Code Sample #2
string xml = "C:/Absolute/Path/To/XmlFile.xml";
string SQL = "SELECT * FROM Tables FOR XML";

SqlConnection connection = new SqlConnection();
connection.ConnectionString = xml;

SqlCommand command = connection.CreateCommand();
command.CommandText = SQL;

SqlDataReader reader = (SqlDataReader)command.BeginExecuteXmlReader();

Error:
System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)

I'm trying to create a SqlDataReader by reading from an xml file to create a set of data. The end goal is to run this as a test case in Gallio, to populate a SQLDataReader with data that causes a bug(instead of going to the db to get the error causing data).

Something like the following:
Assert.AreEqual(1, Convert.ToInt32(reader["ID"])); (and so on...)

The class I'm test against looks something like
public class MyClass
{
private int id;
... other class members

public int ID { get { return id; } }
... other properties

MyClass(SqlDataReader rdr)
{
id = rdr.GetInt32("ID");
... assign other attributes
}
// default constructor is empty
}

3 Replies

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

Lisa17y ago
Glad to hear that you were able to get through

Thanks,
Lisa
Chris Leonard17y ago
I ended up trying what you suggested, I had a couple of minor issues along the way, but it did help. Thanks
Lisa17y ago
have you tried building the schema manually. it would always help
or instead of a datatable use a dataset and add a datatable to it. then try the ds.ReadXMLSchema it should work.

Thanks,
Lisa