Solved

Import XML to SQL 2008 R2

Posted on 2012-04-04
4
554 Views
Last Modified: 2012-04-11
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??
0
Comment
Question by:HDM
4 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37809393
What's the table definition that will be receiving the data?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
ID: 37809904
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 37812321
0
 

Author Comment

by:HDM
ID: 37834815
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question