• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1370
  • Last Modified:

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]);
0
jr_bautista
Asked:
jr_bautista
  • 3
  • 2
1 Solution
 
williamcampbellCommented:
try changing

sbc.DestinationTableName = "Locations";

to

sbc.DestinationTableName = "Location";   // no s
0
 
jr_bautistaAuthor Commented:
"Locations" is the existing  MS SQL table name, it's not the fieldname - why would I change that?
0
 
jr_bautistaAuthor Commented:
Anybody can help me with this? Is this possible with DataSet  and Columnmappings? I appreciate if you could help me asap. Thanks!
0
 
williamcampbellCommented:
Try inserting this code
            sbc.ColumnMappings.Clear();
            foreach ( col In reportData.Tables[0].Columns )
                { sbc.ColumnMappings.Add(col.ColumnName.Trim(), col.ColumnName.Trim()); }
 
             sbc.WriteToServer(reportData.Tables[0]);

Open in new window

0
 
jr_bautistaAuthor Commented:
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!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now