Import XML to SQL 2008 R2

I have the following XML sheet:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <prelimReport>
    <CERTID>49944</CERTID>
    <CERTIFICATENUMBER>C24651</CERTIFICATENUMBER>
    <GROWERNUMBER>23500</GROWERNUMBER>
    <GROWERNAME>123 PRODUCE</GROWERNAME>
    <PRODUCERECEIVEDDATE>2012-03-28T00:00:00-06:00</PRODUCERECEIVEDDATE>
    <INSPECTIONDATE>2012-03-29T00:00:00-06:00</INSPECTIONDATE>
    <TOTALWEIGHT>180320.00000</TOTALWEIGHT>
    <TOTALFEES>72.13000</TOTALFEES>
    <APPLICANTNUMBER>6022</APPLICANTNUMBER>
    <APPLICANT>ABC FOODS</APPLICANT>
    <DISTRICT>ANYWHERE DISTRICT</DISTRICT>
  </prelimReport>
  <prelimReport1>
    <DATAORDER>1</DATAORDER>
    <CERTID>49944</CERTID>
    <FIELDNAME>TOTAL SAMPLE</FIELDNAME>
    <POUNDS>599</POUNDS>
    <PERCENTAGE>0.000000</PERCENTAGE>
  </prelimReport1>
  <prelimReport1>
    <DATAORDER>2</DATAORDER>
    <CERTID>49944</CERTID>
    <FIELDNAME>WATER ADJUSTMENT (1.00%)</FIELDNAME>
    <POUNDS>6</POUNDS>
    <PERCENTAGE>0.000000</PERCENTAGE>
  </prelimReport1>
  <prelimReport1>
    <DATAORDER>4</DATAORDER>
    <CERTID>49944</CERTID>
    <FIELDNAME>TOTAL WATER, ROCK, DIRT, AND FOREIGN MATERIAL</FIELDNAME>
    <POUNDS>17</POUNDS>
    <PERCENTAGE>2.840000</PERCENTAGE>
  </prelimReport1>
  <prelimReport1>
    <DATAORDER>5</DATAORDER>
    <CERTID>49944</CERTID>
    <FIELDNAME>UNGRADED PRODUCE</FIELDNAME>
    <POUNDS>311</POUNDS>
    <PERCENTAGE>0.000000</PERCENTAGE>
  </prelimReport1>
  <prelimReport1>
    <DATAORDER>6</DATAORDER>
    <CERTID>49944</CERTID>
    <FIELDNAME>GRADED PRODUCE</FIELDNAME>
    <POUNDS>277</POUNDS>
    <PERCENTAGE>0.000000</PERCENTAGE>
  </prelimReport1>
  <prelimReport2>
    <TITLETEXT>SUNBURN-GREENING</TITLETEXT>
    <ADJUSTEDWEIGHT>4</ADJUSTEDWEIGHT>
    <DIVISORCALCULATION>1.44000</DIVISORCALCULATION>
    <PRINTPARENS>false</PRINTPARENS>
    <PERCENTAGE>0.00000</PERCENTAGE>
  </prelimReport2>
  <prelimReport2>
    <TITLETEXT>USABLE - 0 MINIMUM</TITLETEXT>
    <ADJUSTEDWEIGHT>273</ADJUSTEDWEIGHT>
    <DIVISORCALCULATION>98.56000</DIVISORCALCULATION>
    <PRINTPARENS>false</PRINTPARENS>
    <PERCENTAGE>0.00000</PERCENTAGE>
  </prelimReport2>
  <prelimReport3>
    <CERTID>49944</CERTID>
    <REMARKS>SAMPLED UNDER FEDERAL-STATE SUPERVISION</REMARKS>
  </prelimReport3>
  <prelimReport4>
    <FIELDLABEL>WEIGHT IN AIR</FIELDLABEL>
    <FIELDVALUE>33.92000</FIELDVALUE>
    <PERCENTAGE>0.00000</PERCENTAGE>
    <FIELDGROUP>GBSPECIFICGRAVITY</FIELDGROUP>
    <GRAVITYDECIMAL>4</GRAVITYDECIMAL>
  </prelimReport4>
  <prelimReport4>
    <FIELDLABEL>WEIGHT IN WATER</FIELDLABEL>
    <FIELDVALUE>2.46000</FIELDVALUE>
    <PERCENTAGE>0.00000</PERCENTAGE>
    <FIELDGROUP>GBSPECIFICGRAVITY</FIELDGROUP>
    <GRAVITYDECIMAL>4</GRAVITYDECIMAL>
  </prelimReport4>
  <prelimReport4>
    <FIELDLABEL>WATER TEMP</FIELDLABEL>
    <FIELDVALUE>55.00000</FIELDVALUE>
    <PERCENTAGE>0.00000</PERCENTAGE>
    <FIELDGROUP>GBSPECIFICGRAVITY</FIELDGROUP>
    <GRAVITYDECIMAL>4</GRAVITYDECIMAL>
  </prelimReport4>
  <prelimReport4>
    <FIELDLABEL>PULP TEMP</FIELDLABEL>
    <FIELDVALUE>55.00000</FIELDVALUE>
    <PERCENTAGE>0.00000</PERCENTAGE>
    <FIELDGROUP>GBSPECIFICGRAVITY</FIELDGROUP>
    <GRAVITYDECIMAL>4</GRAVITYDECIMAL>
  </prelimReport4>
  <prelimReport4>
    <FIELDLABEL>SPECIFIC GRAVITY</FIELDLABEL>
    <FIELDVALUE>1.07880</FIELDVALUE>
    <PERCENTAGE>0.00000</PERCENTAGE>
    <FIELDGROUP>GBSPECIFICGRAVITY</FIELDGROUP>
    <GRAVITYDECIMAL>4</GRAVITYDECIMAL>
  </prelimReport4>
</NewDataSet>

What would be the simplest approch to importing this data into a SQL data base??
Curtis LongAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Do you have some influence on the creation of that XML?  The reason I ask this is because the way the prelimReport nodes are numbered is not really the best way.  It would be better/cleaner to have an attribute to indicate the report, something like:

<prelimReport id="4">


Assuming you want to import the data while splitting out different columns (i.e. not importing into a field of type "xml"), Integration Services would be an option to consider.  Here are a couple of articles that may help to get you started:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9158-Loading-Complex-XML-Using-SSIS.html
0
 
wdosanjosCommented:
What's the table definition that will be receiving the data?
0
 
lcohanConnect With a Mentor Database AnalystCommented:
0
 
Curtis LongAuthor Commented:
Sorry so long replying to this.  

Unfortunately I do not have any influence on the XML sheet.  I get what I get.

I will read through this data and see what else I can figure out.

Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.