Lutz,

My project is not realy for agriculture but its nature prohibits me from using anything that resembles its type so I have made many changes to the original values in my sample.

What I am trying to do is to read a single varbinary(max) that has the original XML text and move the data to a series of SQL 2005 tables. In the example below I need to move the header portion to one row in a table and then use the id assigned to that row by the identity column as foreign key to the others.

The Records portion will be written to a Clients table and the charges to the ClientCharges table. Of course the ID of the Header needs to be written to the Clients and the Client ID will need to be written to the ClientCharges as foreign keys.

The charges need to be written one per row and identified by the associated element tag. Something like ChargeAmount = 500 Type=Fertilizer (two columns in ClientCharges table)

Please keep in mind that the structure of the XML cannot be changed, it's goverment...

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<HC_DATA xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://somefile.xsd">

<HEADER>

<TRANS_CODE>Q</TRANS_CODE>

<RPT_YEAR>2010</RPT_YEAR>

<RPT_QTR>1</RPT_QTR>

<DATA_TYPE>XXX-2</DATA_TYPE>

<SUBMISSION_TYPE>I</SUBMISSION_TYPE>

<PROCESS_DATE>2009-10-13</PROCESS_DATE>

<FACILITY_NUM>00100001</FACILITY_NUM>

<ORG_NAME>XML Data</ORG_NAME>

<CONTACT_PERSON>

<NAME>Mike Smith</NAME>

<PHONE>(888)222-1111</PHONE>

<EMAIL>Smith@anywhere.com</EMAIL>

<STREET>P.O. Box 222</STREET>

<CITY>Somewhere</CITY>

<STATE>CA</STATE>

<ZIP>52309</ZIP>

</CONTACT_PERSON>

</HEADER>

<RECORDS>

<RECORD id="458841">

<FACILITY_NUM>00100001</FACILITY_NUM>

<CLIENT_SSN>111223333</CLIENT_SSN>

<CLIENT_ETHNICITY>E7</CLIENT_ETHNICITY>

<CLIENT_RACE>4</CLIENT_RACE>

<CLIENT_BIRTHDATE>1997-12-24</CLIENT_BIRTHDATE>

<CLIENT_SEX>M</CLIENT_SEX>

<CLIENT_ZIP>40312</CLIENT_ZIP>

<CLIENT_COUNTRY>99</CLIENT_COUNTRY>

<TYPE_OF_SERVICE>1</TYPE_OF_SERVICE>

<PRINC_PAYER_CODE>B</PRINC_PAYER_CODE>

<FERTILIZER_CHARGES>500</FERTILIZER_CHARGES>

<HERBIZIDE_LEVEL_1_CHARGES>0</HERBIZIDE_LEVEL_1_CHARGES>

<HERBIZIDE_LEVEL_2_CHARGES>250</HERBIZIDE_LEVEL_2_CHARGES>

<HERBIZIDE_LEVEL_3_CHARGES>0</HERBIZIDE_LEVEL_3_CHARGES>

<SEED_CHARGES>0</SEED_CHARGES>

<MAINTENANCE_CHARGES>1000</MAINTENANCE_CHARGES>

</RECORD>

</RECORDS>

<TRAILER>

<NUMBER_OF_RECORDS>1</NUMBER_OF_RECORDS>

</TRAILER>

</HC_DATA>