Curtis Long
asked on
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< /CERTIFICA TENUMBER>
<GROWERNUMBER>23500</GROWE RNUMBER>
<GROWERNAME>123 PRODUCE</GROWERNAME>
<PRODUCERECEIVEDDATE>2012- 03-28T00:0 0:00-06:00 </PRODUCER ECEIVEDDAT E>
<INSPECTIONDATE>2012-03-29 T00:00:00- 06:00</INS PECTIONDAT E>
<TOTALWEIGHT>180320.00000< /TOTALWEIG HT>
<TOTALFEES>72.13000</TOTAL FEES>
<APPLICANTNUMBER>6022</APP LICANTNUMB ER>
<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</PERC ENTAGE>
</prelimReport1>
<prelimReport1>
<DATAORDER>2</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>WATER ADJUSTMENT (1.00%)</FIELDNAME>
<POUNDS>6</POUNDS>
<PERCENTAGE>0.000000</PERC ENTAGE>
</prelimReport1>
<prelimReport1>
<DATAORDER>4</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>TOTAL WATER, ROCK, DIRT, AND FOREIGN MATERIAL</FIELDNAME>
<POUNDS>17</POUNDS>
<PERCENTAGE>2.840000</PERC ENTAGE>
</prelimReport1>
<prelimReport1>
<DATAORDER>5</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>UNGRADED PRODUCE</FIELDNAME>
<POUNDS>311</POUNDS>
<PERCENTAGE>0.000000</PERC ENTAGE>
</prelimReport1>
<prelimReport1>
<DATAORDER>6</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>GRADED PRODUCE</FIELDNAME>
<POUNDS>277</POUNDS>
<PERCENTAGE>0.000000</PERC ENTAGE>
</prelimReport1>
<prelimReport2>
<TITLETEXT>SUNBURN-GREENIN G</TITLETE XT>
<ADJUSTEDWEIGHT>4</ADJUSTE DWEIGHT>
<DIVISORCALCULATION>1.4400 0</DIVISOR CALCULATIO N>
<PRINTPARENS>false</PRINTP ARENS>
<PERCENTAGE>0.00000</PERCE NTAGE>
</prelimReport2>
<prelimReport2>
<TITLETEXT>USABLE - 0 MINIMUM</TITLETEXT>
<ADJUSTEDWEIGHT>273</ADJUS TEDWEIGHT>
<DIVISORCALCULATION>98.560 00</DIVISO RCALCULATI ON>
<PRINTPARENS>false</PRINTP ARENS>
<PERCENTAGE>0.00000</PERCE NTAGE>
</prelimReport2>
<prelimReport3>
<CERTID>49944</CERTID>
<REMARKS>SAMPLED UNDER FEDERAL-STATE SUPERVISION</REMARKS>
</prelimReport3>
<prelimReport4>
<FIELDLABEL>WEIGHT IN AIR</FIELDLABEL>
<FIELDVALUE>33.92000</FIEL DVALUE>
<PERCENTAGE>0.00000</PERCE NTAGE>
<FIELDGROUP>GBSPECIFICGRAV ITY</FIELD GROUP>
<GRAVITYDECIMAL>4</GRAVITY DECIMAL>
</prelimReport4>
<prelimReport4>
<FIELDLABEL>WEIGHT IN WATER</FIELDLABEL>
<FIELDVALUE>2.46000</FIELD VALUE>
<PERCENTAGE>0.00000</PERCE NTAGE>
<FIELDGROUP>GBSPECIFICGRAV ITY</FIELD GROUP>
<GRAVITYDECIMAL>4</GRAVITY DECIMAL>
</prelimReport4>
<prelimReport4>
<FIELDLABEL>WATER TEMP</FIELDLABEL>
<FIELDVALUE>55.00000</FIEL DVALUE>
<PERCENTAGE>0.00000</PERCE NTAGE>
<FIELDGROUP>GBSPECIFICGRAV ITY</FIELD GROUP>
<GRAVITYDECIMAL>4</GRAVITY DECIMAL>
</prelimReport4>
<prelimReport4>
<FIELDLABEL>PULP TEMP</FIELDLABEL>
<FIELDVALUE>55.00000</FIEL DVALUE>
<PERCENTAGE>0.00000</PERCE NTAGE>
<FIELDGROUP>GBSPECIFICGRAV ITY</FIELD GROUP>
<GRAVITYDECIMAL>4</GRAVITY DECIMAL>
</prelimReport4>
<prelimReport4>
<FIELDLABEL>SPECIFIC GRAVITY</FIELDLABEL>
<FIELDVALUE>1.07880</FIELD VALUE>
<PERCENTAGE>0.00000</PERCE NTAGE>
<FIELDGROUP>GBSPECIFICGRAV ITY</FIELD GROUP>
<GRAVITYDECIMAL>4</GRAVITY DECIMAL>
</prelimReport4>
</NewDataSet>
What would be the simplest approch to importing this data into a SQL data base??
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<prelimReport>
<CERTID>49944</CERTID>
<CERTIFICATENUMBER>C24651<
<GROWERNUMBER>23500</GROWE
<GROWERNAME>123 PRODUCE</GROWERNAME>
<PRODUCERECEIVEDDATE>2012-
<INSPECTIONDATE>2012-03-29
<TOTALWEIGHT>180320.00000<
<TOTALFEES>72.13000</TOTAL
<APPLICANTNUMBER>6022</APP
<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</PERC
</prelimReport1>
<prelimReport1>
<DATAORDER>2</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>WATER ADJUSTMENT (1.00%)</FIELDNAME>
<POUNDS>6</POUNDS>
<PERCENTAGE>0.000000</PERC
</prelimReport1>
<prelimReport1>
<DATAORDER>4</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>TOTAL WATER, ROCK, DIRT, AND FOREIGN MATERIAL</FIELDNAME>
<POUNDS>17</POUNDS>
<PERCENTAGE>2.840000</PERC
</prelimReport1>
<prelimReport1>
<DATAORDER>5</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>UNGRADED PRODUCE</FIELDNAME>
<POUNDS>311</POUNDS>
<PERCENTAGE>0.000000</PERC
</prelimReport1>
<prelimReport1>
<DATAORDER>6</DATAORDER>
<CERTID>49944</CERTID>
<FIELDNAME>GRADED PRODUCE</FIELDNAME>
<POUNDS>277</POUNDS>
<PERCENTAGE>0.000000</PERC
</prelimReport1>
<prelimReport2>
<TITLETEXT>SUNBURN-GREENIN
<ADJUSTEDWEIGHT>4</ADJUSTE
<DIVISORCALCULATION>1.4400
<PRINTPARENS>false</PRINTP
<PERCENTAGE>0.00000</PERCE
</prelimReport2>
<prelimReport2>
<TITLETEXT>USABLE - 0 MINIMUM</TITLETEXT>
<ADJUSTEDWEIGHT>273</ADJUS
<DIVISORCALCULATION>98.560
<PRINTPARENS>false</PRINTP
<PERCENTAGE>0.00000</PERCE
</prelimReport2>
<prelimReport3>
<CERTID>49944</CERTID>
<REMARKS>SAMPLED UNDER FEDERAL-STATE SUPERVISION</REMARKS>
</prelimReport3>
<prelimReport4>
<FIELDLABEL>WEIGHT IN AIR</FIELDLABEL>
<FIELDVALUE>33.92000</FIEL
<PERCENTAGE>0.00000</PERCE
<FIELDGROUP>GBSPECIFICGRAV
<GRAVITYDECIMAL>4</GRAVITY
</prelimReport4>
<prelimReport4>
<FIELDLABEL>WEIGHT IN WATER</FIELDLABEL>
<FIELDVALUE>2.46000</FIELD
<PERCENTAGE>0.00000</PERCE
<FIELDGROUP>GBSPECIFICGRAV
<GRAVITYDECIMAL>4</GRAVITY
</prelimReport4>
<prelimReport4>
<FIELDLABEL>WATER TEMP</FIELDLABEL>
<FIELDVALUE>55.00000</FIEL
<PERCENTAGE>0.00000</PERCE
<FIELDGROUP>GBSPECIFICGRAV
<GRAVITYDECIMAL>4</GRAVITY
</prelimReport4>
<prelimReport4>
<FIELDLABEL>PULP TEMP</FIELDLABEL>
<FIELDVALUE>55.00000</FIEL
<PERCENTAGE>0.00000</PERCE
<FIELDGROUP>GBSPECIFICGRAV
<GRAVITYDECIMAL>4</GRAVITY
</prelimReport4>
<prelimReport4>
<FIELDLABEL>SPECIFIC GRAVITY</FIELDLABEL>
<FIELDVALUE>1.07880</FIELD
<PERCENTAGE>0.00000</PERCE
<FIELDGROUP>GBSPECIFICGRAV
<GRAVITYDECIMAL>4</GRAVITY
</prelimReport4>
</NewDataSet>
What would be the simplest approch to importing this data into a SQL data base??
What's the table definition that will be receiving the data?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!!
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!!