Solved

Import XML to SQL 2008 R2

Posted on 2012-04-04
4
547 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
Comment Utility
What's the table definition that will be receiving the data?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
Comment Utility
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
Comment Utility
0
 

Author Comment

by:HDM
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SSRS Status field from text to number 2 25
query help 18 50
Concatenating multiple comments into one row 16 29
Row-Level Security 2 14
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard 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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now