asked on

XML, MS SQL, Bulkcopy, dataSet, C#

<Report>
<Event>
<PresentationDate>2009-05-30T09:00:00</PresentationDate>
<SpeakerName>Dr. John Rudy Jess</SpeakerName>
<SpeakerLocation>Dallas, TX</SpeakerLocation>
<Locations>
<Location>
<RepID>1234abcd</RepID>
<PresentationKey>D01</PresentationKey>
<VenueID>1234</VenueID>
</Location>
</Locations>
</Event>
</Report>

If I have this XML structure, how can I parse this to write to dataset then to MS SQL
I have no problem with the root node but when it comes to the nested ones it cannot recoginize the field like for e.x. repID

Here's my code:

DataSet reportData = new DataSet();
reportData.ReadXml("C:\\XML\\" + tableName);

SqlBulkCopy sbc = new SqlBulkCopy(sqlConn);
sbc.DestinationTableName = "Locations";

sbc.ColumnMappings.Add("RepID", "RepID");
sbc.ColumnMappings.Add("PresentationKey", "PresentationKey");
sbc.ColumnMappings.Add("VenueID", "VenueID");

sbc.WriteToServer(reportData.Tables[0]);

williamcampbell

try changing

sbc.DestinationTableName = "Locations";

to

sbc.DestinationTableName = "Location"; // no s

jr_bautista

ASKER

"Locations" is the existing MS SQL table name, it's not the fieldname - why would I change that?

jr_bautista

ASKER

Anybody can help me with this? Is this possible with DataSet and Columnmappings? I appreciate if you could help me asap. Thanks!

ASKER CERTIFIED SOLUTION

williamcampbell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jr_bautista

ASKER

I think you have to write on Tables no as it corresponds to hierarchy in XML document. Tables[0] refers on the root, Tables[2] next one and so on....thanks!