XML : XML Data and SQL Bulk Copy

I have XML Data that I am inserting into a SQL Table

  <data>  <racedata>  <race>1</race>  <todays_cls>97</todays_cls>  </racedata>  <stats_data>  <stat type="ALL_WEATHR">  <starts>0</starts>  <wins>0</wins>  <places>0</places>  <shows>0</shows>  <earnings>0.00</earnings>  <paid>0.00</paid>  <roi />  </stat>                                                                                              <stat type="AT_DST_CRS">  <starts>13</starts>  <wins>4</wins>  <places>1</places>  <shows>0</shows>  <earnings>93448.00</earnings>  <paid>12.00</paid>  <roi>23</roi>  </stat>  </stats_data>  <data>    

The racedata inserts fine using SQLBulkCopy. As an example:

  bc.DestinationTableName = "racedata";  bc.ColumnMappings.Add("race", "race");  bc.ColumnMappings.Add("todays_cls", "todays_cls");     

The issue is when I try to insert the stats_data. When I try to capture the different stat categories like ALL_WEATHR and AT_DST_CRS, I get an error message because the data source and the target table columns have to have the same name. However, ALL_WEATHR and AT_DST_CRS are not column names, they are values. They tell how may wins, places, shows, etc on each type of surface. How would I go about inserting them into a SQL Table?

No comments:

Post a Comment