Reading data from XML file and adding to SQL Server

[David]

Suppose I have the following database:

CREATE TABLE Locations
(
int id identity not null,
city varchar(50)
)

CREATE TABLE People
(
name varchar(100),
int fk_Location references Locations(id)
)

And I have an XML file that looks like:
<People name="Fred" location="Atlanta" />
<People name="Sally" location="Atlanta" />
<People name="John" location="Los Angeles" />
<People name="Mark" location="New York" />

I want to add a record to People for every <People> in the XML file.
If there is a row in Locations where city equals the "location"
attribute in <People>, I want fk_Location equal to the existing
Locations.id, otherwise I want to create a new row in Locations where
city is the "location" attribute.

I know I can create a blank DataSet and use DataSet.ReadXml(), but
this creates a People table with a location column, rather than a
foreign key to a Locations table.

What's the best way to accomplish this? (Preferably in C#)

Thanks

 

[Kathleen Dollard]

David,

You'll have to either do this via XSLT before you load the data (and its not
totally straightforward to do this) or you'll have to import your data row
by row and build the table manually, which would be easier. You can use the
XMLTextReader because you'll just need single pass access.

 

[Guest]

Highly unstructured, but working code (written hastily, the code can be well-refactored)
import the related libraries & change connstr. rest all should be OK

public static void check()
{
try
{
OleDbConnection cnn = new OleDbConnection("Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=Kalpesh;Initial Catalog=Northwind;Data Source=KALPESH;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=KALPESH;Use Encryption for Data=False;Tag with column collation when
possible=False");
cnn.Open();

OleDbDataAdapter daloc = new OleDbDataAdapter("SELECT * FROM LOCATIONS", cnn);

OleDbCommand oleInsertCommand1 = new OleDbCommand();
oleInsertCommand1.CommandText = "INSERT INTO Locations(city) VALUES (?); SELECT cid, city FROM Locations WHERE" +
" (cid = @@IDENTITY)";
oleInsertCommand1.Connection = cnn;
oleInsertCommand1.Parameters.Add(new OleDbParameter("city", OleDbType.VarChar, 50, "city"));

daloc.InsertCommand = oleInsertCommand1;


OleDbDataAdapter dappl = new OleDbDataAdapter("SELECT * FROM PEOPLE", cnn);
OleDbCommand oleInsertCommand2 = new OleDbCommand();
oleInsertCommand2.CommandText = "INSERT INTO People(name, location) VALUES (?, ?); SELECT name, location FROM People";
oleInsertCommand2.Connection = cnn;
oleInsertCommand2.Parameters.Add(new OleDbParameter("name", OleDbType.VarChar, 100, "name"));
oleInsertCommand2.Parameters.Add(new OleDbParameter("location", OleDbType.Integer, 4, "location"));
dappl.InsertCommand = oleInsertCommand2;


DataSet ds = new DataSet();
daloc.Fill(ds, "Locations");
dappl.Fill(ds, "People");

ds.Tables["Locations"].Columns[0].AutoIncrement = true;
ds.Tables["Locations"].Columns[0].Unique = true;
ds.Tables["Locations"].Columns["city"].Unique = true;
ds.Tables["Locations"].Columns[0].AutoIncrementSeed=1;

ds.Relations.Add(ds.Tables["Locations"].Columns[0], ds.Tables["People"].Columns[1]);


XmlTextReader xtr = new XmlTextReader(@"<People name=""Fred"" location=""Atlanta"" /><People name=""Sally""
location=""Atlanta"" /><People name=""John"" location=""Los Angeles"" /><People name=""Mark"" location=""New York""
/><People name=""Kalpesh"" location=""Atlanta"" /><People name=""K1"" location=""Los Angeles"" /><People name=""K2""
location=""Mumbai"" />", XmlNodeType.Element, null);

String loc = "";
String name = "";
DataRow dr = null, prevdr = null;
DataRow cdr = null;

while (xtr.Read())
{
loc = xtr.GetAttribute("location");
name = xtr.GetAttribute("name");

if (loc.Trim() != "")
{
try
{
prevdr = dr;
dr = ds.Tables["Locations"].NewRow();
dr["city"] = loc;
ds.Tables["Locations"].Rows.Add(dr);
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
dr = ds.Tables["Locations"].Select("city = '" + loc + "'")[0];
}
}
cdr = ds.Tables["People"].NewRow();
cdr["location"] = dr["cid"];
cdr["name"] = name;
ds.Tables["People"].Rows.Add(cdr);
}
Console.WriteLine(ds.Tables["Locations"].Rows.Count.ToString());
//ds.AcceptChanges();
daloc.Update(ds, "Locations");
dappl.Update(ds, "People");
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
}
}


HTH
Kalpesh

 

[David]

Thanks, that was the way I ended up doing it. I was hoping there was
some way to do it auto-magically, but reading it in manually was not
that much harder.