Solved

Using OPENXML to insert XML data into a table

Posted on 2006-10-25
22
444 Views
Last Modified: 2013-11-19
Hi,

I have the following XML

    <Crops>
      <Crop xmlns="http://www.helloFriend.com">
        <Name>Feed Wheat</Name>
        <UnitValue>0</UnitValue>
        <TonnesPerUnit>0</TonnesPerUnit>
        <TotalTonnes>0</TotalTonnes>
        <VariableCosts>
          <SeedsPerUnit>0</SeedsPerUnit>
          <FertiliserPerUnit>0</FertiliserPerUnit>
          <HerbicidesPerUnit>0</HerbicidesPerUnit>
          <FungicidesPerUnit>0</FungicidesPerUnit>
          <InsecticidesPerUnit>0</InsecticidesPerUnit>
          <OtherPerUnit>0</OtherPerUnit>
          <TotalSpraysPerUnit>0</TotalSpraysPerUnit>
          <SundriesPerUnit>0</SundriesPerUnit>
          <TotalSeeds>0</TotalSeeds>
          <TotalFertiliser>0</TotalFertiliser>
          <TotalHerbicides>0</TotalHerbicides>
          <TotalFungicides>0</TotalFungicides>
          <TotalInsecticides>0</TotalInsecticides>
          <TotalOther>0</TotalOther>
          <TotalSpraysTotal>0</TotalSpraysTotal>
          <TotalSundries>0</TotalSundries>
        </VariableCosts>
        <DistributionPerUnitLabour>0</DistributionPerUnitLabour>
        <DistributionTotalLabour>0</DistributionTotalLabour>
        <DistributionPerUnitMachinery>0</DistributionPerUnitMachinery>
        <DistributionTotalMachinery>0</DistributionTotalMachinery>
        <DistributionPerUnitLabourW>0</DistributionPerUnitLabourW>
        <DistributionTotalLabourW>0</DistributionTotalLabourW>
        <DistributionPerUnitMachineryW>0</DistributionPerUnitMachineryW>
        <DistributionTotalMachineryW>0</DistributionTotalMachineryW>
        <TotalPerUnitCost>0</TotalPerUnitCost>
        <TotalTotalCost>0</TotalTotalCost>
        <TotalCostPerTonne>0</TotalCostPerTonne>
      </Crop>
      <Crop xmlns="http://www.helloFriend.com">
        <Name>Oats</Name>
        <UnitValue>0</UnitValue>
        <TonnesPerUnit>0</TonnesPerUnit>
        <TotalTonnes>0</TotalTonnes>
        <VariableCosts>
          <SeedsPerUnit>0</SeedsPerUnit>
          <FertiliserPerUnit>0</FertiliserPerUnit>
          <HerbicidesPerUnit>0</HerbicidesPerUnit>
          <FungicidesPerUnit>0</FungicidesPerUnit>
          <InsecticidesPerUnit>0</InsecticidesPerUnit>
          <OtherPerUnit>0</OtherPerUnit>
          <TotalSpraysPerUnit>0</TotalSpraysPerUnit>
          <SundriesPerUnit>0</SundriesPerUnit>
          <TotalSeeds>0</TotalSeeds>
          <TotalFertiliser>0</TotalFertiliser>
          <TotalHerbicides>0</TotalHerbicides>
          <TotalFungicides>0</TotalFungicides>
          <TotalInsecticides>0</TotalInsecticides>
          <TotalOther>0</TotalOther>
          <TotalSpraysTotal>0</TotalSpraysTotal>
          <TotalSundries>0</TotalSundries>
        </VariableCosts>
        <DistributionPerUnitLabour>0</DistributionPerUnitLabour>
        <DistributionTotalLabour>0</DistributionTotalLabour>
        <DistributionPerUnitMachinery>0</DistributionPerUnitMachinery>
        <DistributionTotalMachinery>0</DistributionTotalMachinery>
        <DistributionPerUnitLabourW>0</DistributionPerUnitLabourW>
        <DistributionTotalLabourW>0</DistributionTotalLabourW>
        <DistributionPerUnitMachineryW>0</DistributionPerUnitMachineryW>
        <DistributionTotalMachineryW>0</DistributionTotalMachineryW>
        <TotalPerUnitCost>0</TotalPerUnitCost>
        <TotalTotalCost>0</TotalTotalCost>
        <TotalCostPerTonne>0</TotalCostPerTonne>
      </Crop>
    </Crops>

I have a table called bm_crops which has columns exactly named as the crop chi8ld elements, including the child elelments of the VariableCosts.  There are more than 1 crop elements in the crops element.  I would like to write each crop element as a row into the table.  Note there is only 1 VariableCosts element in a crop element.

I hope I am making sense.  I tried the following (as well as putting @ symbol before attributes).  All seems OK but it doesn't write anything to the table, not even NULLS.  (the first table gets populated and we get back a bmId)

ALTER Procedure bm_registeredData_load_xml
(
      @xmlDoc ntext = null,
      @userId int = null
)
AS
      SET NOCOUNT ON
      DECLARE @idoc int, @bmId int, @year int

      SET @bmId = 0

      -- Check we have some data to work with.
      IF @xmlDoc IS NOT NULL
      BEGIN
            EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc

            -- Add to bm_registeredData
            INSERT INTO
                  bm_registeredData
            SELECT
                  @userId AS userId, HarvestYear, GroupID, CreatedDate, SavedDate, Comments, DateUploaded, Valid
            FROM
                  OPENXML (@idoc, '/*', 2) WITH bm_registeredData            

            -- Store the identy ID.
            SELECT @bmId = @@IDENTITY

            -- Add to bm_crops
            INSERT INTO
                  bm_crops
            SELECT
                  @bmId AS bmId, [Name], UnitValue, TonnesPerUnit = ISNULL(TonnesPerUnit ,0), TotalTonnes = ISNULL(TotalTonnes ,0), DistributionPerUnitLabour, DistributionTotalLabour,
                  DistributionPerUnitMachinery, DistributionTotalMachinery, DistributionPerUnitLabourW, DistributionTotalLabourW,
                  DistributionPerUnitMachineryW, DistributionTotalMachineryW, TotalPerUnitCost, TotalTotalCost, TotalCostPerTonne,
                  SeedsPerUnit, FertiliserPerUnit, HerbicidesPerUnit, FungicidesPerUnit, InsecticidesPerUnit, OtherPerUnit,
                  TotalSpraysPerUnit, SundriesPerUnit, TotalSeeds, TotalFertiliser, TotalHerbicides, TotalFungicides,
                  TotalInsecticides, TotalOther, TotalSpraysTotal, TotalSundries                  
            FROM
                  OPENXML (@idoc, '/Benchmark/FarmDetails/Crops/Crop/VariableCosts', 2)
        WITH (bmId      int                                                            ,
                        [Name]      varchar      (100)                                    '../Name',
                        UnitValue      numeric                                          '../UnitValue',
                        TonnesPerUnit      numeric                                    '../TonnesPerUnit',
                        TotalTonnes      numeric                                          '../TotalTonnes',
                        DistributionPerUnitLabour      numeric                  '../DistributionPerUnitLabour',
                        DistributionTotalLabour      numeric                        '../DistributionTotalLabour',
                        DistributionPerUnitMachinery      numeric            '../DistributionPerUnitMachinery',
                        DistributionTotalMachinery      numeric                  '../DistributionTotalMachinery',
                        DistributionPerUnitLabourW      numeric                  '../DistributionPerUnitLabourW',
                        DistributionTotalLabourW      numeric                  '../DistributionTotalLabourW',
                        DistributionPerUnitMachineryW      numeric            '../DistributionPerUnitMachineryW',
                        DistributionTotalMachineryW      numeric                  '../DistributionTotalMachineryW',
                        TotalPerUnitCost      numeric                              '../TotalPerUnitCost',
                        TotalTotalCost      numeric                                    '../TotalTotalCost',
                        TotalCostPerTonne      numeric                              '../TotalCostPerTonne',
                        SeedsPerUnit      numeric                                    'SeedsPerUnit',
                        FertiliserPerUnit      numeric                              'FertiliserPerUnit',
                        HerbicidesPerUnit      numeric                              'HerbicidesPerUnit',
                        FungicidesPerUnit      numeric                              'FungicidesPerUnit',
                        InsecticidesPerUnit      numeric                              'InsecticidesPerUnit',
                        OtherPerUnit      numeric                                    'OtherPerUnit',
                        TotalSpraysPerUnit      numeric                              'TotalSpraysPerUnit',
                        SundriesPerUnit      numeric                                    'SundriesPerUnit',
                        TotalSeeds      numeric                                          'TotalSeeds',
                        TotalFertiliser      numeric                                    'TotalFertiliser',
                        TotalHerbicides      numeric                                    'TotalHerbicides',
                        TotalFungicides      numeric                                    'TotalFungicides',
                        TotalInsecticides      numeric                              'TotalInsecticides',
                        TotalOther      numeric                                          'TotalOther',
                        TotalSpraysTotal      numeric                              'TotalSpraysTotal',
                        TotalSundries      numeric                                    'TotalSundries')
                  


            -- Release resources.
            EXEC sp_xml_removedocument @idoc

      END

      -- Return the record identity.
      RETURN @bmId


What am I doing wrong?

Hass
0
Comment
Question by:gbzhhu
  • 12
  • 10
22 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17809154
>>What am I doing wrong?<<
You are overlooking the fact that you have a namespace:  
You have not declared the namespace in your sp_xml_preparedocument, it should be something like this:
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc, '<root xmlns:ns="http://www.helloFriend.com"/>'

You are using an invalid XPath:  There are no Benchmark or FarmDetails nodes.

>> I would like to write each crop element as a row into the table.<<
Than you should not include VariableCosts in your XPath.

You have not included the namespace in the individual elements.

Using the above mentioned sp_xml_preparedocument, your Insert statement should look like this:

INSERT      bm_crops
SELECT      [Name],
            UnitValue,
            TonnesPerUnit = ISNULL(TonnesPerUnit ,0),
            TotalTonnes = ISNULL(TotalTonnes ,0),
            DistributionPerUnitLabour,
            DistributionTotalLabour,
            DistributionPerUnitMachinery,
            DistributionTotalMachinery,
            DistributionPerUnitLabourW,
            DistributionTotalLabourW,
            DistributionPerUnitMachineryW,
            DistributionTotalMachineryW,
            TotalPerUnitCost,
            TotalTotalCost,
            TotalCostPerTonne,
            SeedsPerUnit,
            FertiliserPerUnit,
            HerbicidesPerUnit,
            FungicidesPerUnit,
            InsecticidesPerUnit,
            OtherPerUnit,
            TotalSpraysPerUnit,
            SundriesPerUnit,
            TotalSeeds,
            TotalFertiliser,
            TotalHerbicides,
            TotalFungicides,
            TotalInsecticides,
            TotalOther,
            TotalSpraysTotal,
            TotalSundries
FROM      OPENXML (@idoc, 'Crops/ns:Crop', 2) WITH (
                  [Name] varchar(100) 'ns:Name',
                  UnitValue numeric 'ns:UnitValue',
                  TonnesPerUnit numeric 'ns:TonnesPerUnit',
                  TotalTonnes numeric 'ns:TotalTonnes',
                  DistributionPerUnitLabour numeric 'ns:DistributionPerUnitLabour',
                  DistributionTotalLabour numeric 'ns:DistributionTotalLabour',
                  DistributionPerUnitMachinery numeric 'ns:DistributionPerUnitMachinery',
                  DistributionTotalMachinery numeric 'ns:DistributionTotalMachinery',
                  DistributionPerUnitLabourW numeric 'ns:DistributionPerUnitLabourW',
                  DistributionTotalLabourW numeric 'ns:DistributionTotalLabourW',
                  DistributionPerUnitMachineryW numeric 'ns:DistributionPerUnitMachineryW',
                  DistributionTotalMachineryW numeric 'ns:DistributionTotalMachineryW',
                  TotalPerUnitCost numeric 'ns:TotalPerUnitCost',
                  TotalTotalCost numeric 'ns:TotalTotalCost',
                  TotalCostPerTonne numeric 'ns:TotalCostPerTonne',
                  SeedsPerUnit numeric 'ns:VariableCosts/ns:SeedsPerUnit',
                  FertiliserPerUnit numeric 'ns:VariableCosts/ns:FertiliserPerUnit',
                  HerbicidesPerUnit numeric 'ns:VariableCosts/ns:HerbicidesPerUnit',
                  FungicidesPerUnit numeric 'ns:VariableCosts/ns:FungicidesPerUnit',
                  InsecticidesPerUnit numeric 'ns:VariableCosts/ns:InsecticidesPerUnit',
                  OtherPerUnit numeric 'ns:VariableCosts/ns:OtherPerUnit',
                  TotalSpraysPerUnit numeric 'ns:VariableCosts/ns:TotalSpraysPerUnit',
                  SundriesPerUnit numeric 'ns:VariableCosts/ns:SundriesPerUnit',
                  TotalSeeds numeric 'ns:VariableCosts/ns:TotalSeeds',
                  TotalFertiliser numeric 'ns:VariableCosts/ns:TotalFertiliser',
                  TotalHerbicides numeric 'ns:VariableCosts/ns:TotalHerbicides',
                  TotalFungicides numeric 'ns:VariableCosts/ns:TotalFungicides',
                  TotalInsecticides numeric 'ns:VariableCosts/ns:TotalInsecticides',
                  TotalOther numeric 'ns:VariableCosts/ns:TotalOther',
                  TotalSpraysTotal numeric 'ns:VariableCosts/ns:TotalSpraysTotal',
                  TotalSundries numeric 'ns:VariableCosts/ns:TotalSundries')

EXEC sp_xml_removedocument @idoc

This works for me.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17809161
Totally unrelated, but you would be better off using integer than numeric.
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17810338
Hi acperkins,

Thank you for your post.  This is my first time writing something like this.  I am going to try it and let you know.

>>You are overlooking the fact that you have a namespace:  
This is completely new to me

>>You are using an invalid XPath:  There are no Benchmark or FarmDetails nodes.
My mistake here.  The XML was too big so I only posted the crops section and forgot to remove the parts of the sproc that deal with these elements

>>Than you should not include VariableCosts in your XPath.
How is this achieved?  I mean how does not including VariableCosts in my XPath help? would be kind of you to explain

>>You have not included the namespace in the individual elements.
Again new to me, should invest sometime to learn about namespaces in SQL

Hass
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17810367
Hi,

About the numeric usage, my numbers have decimal places (12.35, 0.02 etc).  I though integer wouldn't represent this correctly.  Am I mistaken?
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17810476
Hi acperkins ,

I believe I have done what you suggested but still don't get any data in the table (I made sure the XML has the right data) and there are no errors.  Just to be sure below are the changes I made

My sp_xml_preparedocument is now like this.

            EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc, '<root xmlns:ns="http://www.helloFriend.com"/>'

Changed the insert and it looks like this now

            -- Add to bm_crops
            INSERT INTO
                  bm_crops
            SELECT
                  @bmId AS bmId, [Name], UnitValue, TonnesPerUnit, TotalTonnes, DistributionPerUnitLabour, DistributionTotalLabour,
                  DistributionPerUnitMachinery, DistributionTotalMachinery, DistributionPerUnitLabourW, DistributionTotalLabourW,
                  DistributionPerUnitMachineryW, DistributionTotalMachineryW, TotalPerUnitCost, TotalTotalCost, TotalCostPerTonne,
                  SeedsPerUnit, FertiliserPerUnit, HerbicidesPerUnit, FungicidesPerUnit, InsecticidesPerUnit, OtherPerUnit,
                  TotalSpraysPerUnit, SundriesPerUnit, TotalSeeds, TotalFertiliser, TotalHerbicides, TotalFungicides,
                  TotalInsecticides, TotalOther, TotalSpraysTotal, TotalSundries                  
            FROM
                  OPENXML (@idoc, 'Crops/ns:Crop', 2)
        WITH (bmId      int                                                            ,
                        [Name]      varchar      (100)                                    'ns:Name',
                        UnitValue      numeric                                          'ns:UnitValue',
                        TonnesPerUnit      numeric                                    'ns:TonnesPerUnit',
                        TotalTonnes      numeric                                          'ns:TotalTonnes',
                        DistributionPerUnitLabour      numeric                  'ns:DistributionPerUnitLabour',
                        DistributionTotalLabour      numeric                        'ns:DistributionTotalLabour',
                        DistributionPerUnitMachinery      numeric            'ns:DistributionPerUnitMachinery',
                        DistributionTotalMachinery      numeric                  'ns:DistributionTotalMachinery',
                        DistributionPerUnitLabourW      numeric                  'ns:DistributionPerUnitLabourW',
                        DistributionTotalLabourW      numeric                  'ns:DistributionTotalLabourW',
                        DistributionPerUnitMachineryW      numeric            'ns:DistributionPerUnitMachineryW',
                        DistributionTotalMachineryW      numeric                  'ns:DistributionTotalMachineryW',
                        TotalPerUnitCost      numeric                              'ns:TotalPerUnitCost',
                        TotalTotalCost      numeric                                    'ns:TotalTotalCost',
                        TotalCostPerTonne      numeric                              'ns:TotalCostPerTonne',
                        SeedsPerUnit      numeric                                    'ns:VariableCosts/ns:SeedsPerUnit',
                        FertiliserPerUnit      numeric                              'ns:VariableCosts/ns:FertiliserPerUnit',
                        HerbicidesPerUnit      numeric                              'ns:VariableCosts/ns:HerbicidesPerUnit',
                        FungicidesPerUnit      numeric                              'ns:VariableCosts/ns:FungicidesPerUnit',
                        InsecticidesPerUnit      numeric                              'ns:VariableCosts/ns:InsecticidesPerUnit',
                        OtherPerUnit      numeric                                    'ns:VariableCosts/ns:OtherPerUnit',
                        TotalSpraysPerUnit      numeric                              'ns:VariableCosts/ns:TotalSpraysPerUnit',
                        SundriesPerUnit      numeric                                    'ns:VariableCosts/ns:SundriesPerUnit',
                        TotalSeeds      numeric                                          'ns:VariableCosts/ns:TotalSeeds',
                        TotalFertiliser      numeric                                    'ns:VariableCosts/ns:TotalFertiliser',
                        TotalHerbicides      numeric                                    'ns:VariableCosts/ns:TotalHerbicides',
                        TotalFungicides      numeric                                    'ns:VariableCosts/ns:TotalFungicides',
                        TotalInsecticides      numeric                              'ns:VariableCosts/ns:TotalInsecticides',
                        TotalOther      numeric                                          'ns:VariableCosts/ns:TotalOther',
                        TotalSpraysTotal      numeric                              'ns:VariableCosts/ns:TotalSpraysTotal',
                        TotalSundries      numeric                                    'ns:VariableCosts/ns:TotalSundries')
                  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17811955
Try it this way in SQL Query Analyzer:

Declare @xmlDoc varchar(8000)

Set @xmlDoc =
'<Crops>
      <Crop xmlns="http://www.helloFriend.com">
            <Name>Feed Wheat</Name>
            <UnitValue>0</UnitValue>
            <TonnesPerUnit>0</TonnesPerUnit>
            <TotalTonnes>0</TotalTonnes>
            <VariableCosts>
                  <SeedsPerUnit>0</SeedsPerUnit>
                  <FertiliserPerUnit>0</FertiliserPerUnit>
                  <HerbicidesPerUnit>0</HerbicidesPerUnit>
                  <FungicidesPerUnit>0</FungicidesPerUnit>
                  <InsecticidesPerUnit>0</InsecticidesPerUnit>
                  <OtherPerUnit>0</OtherPerUnit>
                  <TotalSpraysPerUnit>0</TotalSpraysPerUnit>
                  <SundriesPerUnit>0</SundriesPerUnit>
                  <TotalSeeds>0</TotalSeeds>
                  <TotalFertiliser>0</TotalFertiliser>
                  <TotalHerbicides>0</TotalHerbicides>
                  <TotalFungicides>0</TotalFungicides>
                  <TotalInsecticides>0</TotalInsecticides>
                  <TotalOther>0</TotalOther>
                  <TotalSpraysTotal>0</TotalSpraysTotal>
                  <TotalSundries>0</TotalSundries>
            </VariableCosts>
            <DistributionPerUnitLabour>0</DistributionPerUnitLabour>
            <DistributionTotalLabour>0</DistributionTotalLabour>
            <DistributionPerUnitMachinery>0</DistributionPerUnitMachinery>
            <DistributionTotalMachinery>0</DistributionTotalMachinery>
            <DistributionPerUnitLabourW>0</DistributionPerUnitLabourW>
            <DistributionTotalLabourW>0</DistributionTotalLabourW>
            <DistributionPerUnitMachineryW>0</DistributionPerUnitMachineryW>
            <DistributionTotalMachineryW>0</DistributionTotalMachineryW>
            <TotalPerUnitCost>0</TotalPerUnitCost>
            <TotalTotalCost>0</TotalTotalCost>
            <TotalCostPerTonne>0</TotalCostPerTonne>
      </Crop>
      <Crop xmlns="http://www.helloFriend.com">
            <Name>Oats</Name>
            <UnitValue>0</UnitValue>
            <TonnesPerUnit>0</TonnesPerUnit>
            <TotalTonnes>0</TotalTonnes>
            <VariableCosts>
                  <SeedsPerUnit>0</SeedsPerUnit>
                  <FertiliserPerUnit>0</FertiliserPerUnit>
                  <HerbicidesPerUnit>0</HerbicidesPerUnit>
                  <FungicidesPerUnit>0</FungicidesPerUnit>
                  <InsecticidesPerUnit>0</InsecticidesPerUnit>
                  <OtherPerUnit>0</OtherPerUnit>
                  <TotalSpraysPerUnit>0</TotalSpraysPerUnit>
                  <SundriesPerUnit>0</SundriesPerUnit>
                  <TotalSeeds>0</TotalSeeds>
                  <TotalFertiliser>0</TotalFertiliser>
                  <TotalHerbicides>0</TotalHerbicides>
                  <TotalFungicides>0</TotalFungicides>
                  <TotalInsecticides>0</TotalInsecticides>
                  <TotalOther>0</TotalOther>
                  <TotalSpraysTotal>0</TotalSpraysTotal>
                  <TotalSundries>0</TotalSundries>
            </VariableCosts>
            <DistributionPerUnitLabour>0</DistributionPerUnitLabour>
            <DistributionTotalLabour>0</DistributionTotalLabour>
            <DistributionPerUnitMachinery>0</DistributionPerUnitMachinery>
            <DistributionTotalMachinery>0</DistributionTotalMachinery>
            <DistributionPerUnitLabourW>0</DistributionPerUnitLabourW>
            <DistributionTotalLabourW>0</DistributionTotalLabourW>
            <DistributionPerUnitMachineryW>0</DistributionPerUnitMachineryW>
            <DistributionTotalMachineryW>0</DistributionTotalMachineryW>
            <TotalPerUnitCost>0</TotalPerUnitCost>
            <TotalTotalCost>0</TotalTotalCost>
            <TotalCostPerTonne>0</TotalCostPerTonne>
      </Crop>
</Crops>'

Declare @idoc integer
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc, '<root xmlns:ns="http://www.helloFriend.com"/>'


SELECT      [Name], UnitValue, TonnesPerUnit, TotalTonnes, DistributionPerUnitLabour, DistributionTotalLabour,
      DistributionPerUnitMachinery, DistributionTotalMachinery, DistributionPerUnitLabourW, DistributionTotalLabourW,
      DistributionPerUnitMachineryW, DistributionTotalMachineryW, TotalPerUnitCost, TotalTotalCost, TotalCostPerTonne,
      SeedsPerUnit, FertiliserPerUnit, HerbicidesPerUnit, FungicidesPerUnit, InsecticidesPerUnit, OtherPerUnit,
      TotalSpraysPerUnit, SundriesPerUnit, TotalSeeds, TotalFertiliser, TotalHerbicides, TotalFungicides,
      TotalInsecticides, TotalOther, TotalSpraysTotal, TotalSundries              
FROM      OPENXML (@idoc, 'Crops/ns:Crop', 2) WITH (
                [Name] varchar(100) 'ns:Name',
                UnitValue numeric 'ns:UnitValue',
                TonnesPerUnit numeric 'ns:TonnesPerUnit',
                TotalTonnes numeric 'ns:TotalTonnes',
                DistributionPerUnitLabour numeric 'ns:DistributionPerUnitLabour',
                DistributionTotalLabour numeric 'ns:DistributionTotalLabour',
                DistributionPerUnitMachinery numeric 'ns:DistributionPerUnitMachinery',
                DistributionTotalMachinery numeric 'ns:DistributionTotalMachinery',
                DistributionPerUnitLabourW numeric 'ns:DistributionPerUnitLabourW',
                DistributionTotalLabourW numeric 'ns:DistributionTotalLabourW',
                DistributionPerUnitMachineryW numeric 'ns:DistributionPerUnitMachineryW',
                DistributionTotalMachineryW numeric 'ns:DistributionTotalMachineryW',
                TotalPerUnitCost numeric 'ns:TotalPerUnitCost',
                TotalTotalCost numeric 'ns:TotalTotalCost',
                TotalCostPerTonne numeric 'ns:TotalCostPerTonne',
                SeedsPerUnit numeric 'ns:VariableCosts/ns:SeedsPerUnit',
                FertiliserPerUnit numeric 'ns:VariableCosts/ns:FertiliserPerUnit',
                HerbicidesPerUnit numeric 'ns:VariableCosts/ns:HerbicidesPerUnit',
                FungicidesPerUnit numeric 'ns:VariableCosts/ns:FungicidesPerUnit',
                InsecticidesPerUnit numeric 'ns:VariableCosts/ns:InsecticidesPerUnit',
                OtherPerUnit numeric 'ns:VariableCosts/ns:OtherPerUnit',
                TotalSpraysPerUnit numeric 'ns:VariableCosts/ns:TotalSpraysPerUnit',
                SundriesPerUnit numeric 'ns:VariableCosts/ns:SundriesPerUnit',
                TotalSeeds numeric 'ns:VariableCosts/ns:TotalSeeds',
                TotalFertiliser numeric 'ns:VariableCosts/ns:TotalFertiliser',
                TotalHerbicides numeric 'ns:VariableCosts/ns:TotalHerbicides',
                TotalFungicides numeric 'ns:VariableCosts/ns:TotalFungicides',
                TotalInsecticides numeric 'ns:VariableCosts/ns:TotalInsecticides',
                TotalOther numeric 'ns:VariableCosts/ns:TotalOther',
                TotalSpraysTotal numeric 'ns:VariableCosts/ns:TotalSpraysTotal',
                TotalSundries numeric 'ns:VariableCosts/ns:TotalSundries')

EXEC sp_xml_removedocument @idoc

You should get 2 rows returned.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17812075
>>How is this achieved?  I mean how does not including VariableCosts in my XPath help? would be kind of you to explain<<
You first need to define what is driving the number of rows.  Since you stated: "I would like to write each crop element as a row into the table.", than that tells me that "Crops/Crop" is the correct XPath.  What elements are actually retrieved in each row is a function of each element, but the driver is in the XPath in OPENXML

>>Again new to me, should invest sometime to learn about namespaces in SQL<<
Actually namespaces are an Xml property, SQL just inherited them :)

>>About the numeric usage, my numbers have decimal places (12.35, 0.02 etc).  <<
Than you need to define them correctly, as in numeric(18, 2) otherwise numeric will lose the decimals.  Try the following in SQL Query Analyzer and you will see what I mean:
Declare @N numeric
Set @N = 12.35

Select @N
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17812214
Re - your previous post, I tried code in Query Analyzer and got the 2 records as you said.  The difference is the bmId.  I added a hardcoded bmId

Declare @bmId int

Set @bmId = 43

And I still get the correct data returned along woth the bmId.  My code doesn't work in the app though, must be something else.  I am posting the entire xml and the entire sproc and hopefully you would be able to help me pinpoint what is missing

Re - your last post,  the XPath explanation makes sense thanks.

I am familiar with namespaces in programming (c# etc as well as in XML) but didn't know they are in SQL and how to them in SQL

Good point about the numeric data.  I wouldn't have noticed until I managed to get data into the tables, cheers for that

================= xml =======================

<?xml version="1.0" encoding="utf-8"?>
<Benchmark xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <FarmDetails>
    <Crops>
      <Crop xmlns="http://www.helloFriend.com">
        <Name>Feed Wheat</Name>
        <UnitValue>0</UnitValue>
        <TonnesPerUnit>0</TonnesPerUnit>
        <TotalTonnes>0</TotalTonnes>
        <VariableCosts>
          <SeedsPerUnit>0</SeedsPerUnit>
          <FertiliserPerUnit>0</FertiliserPerUnit>
          <HerbicidesPerUnit>0</HerbicidesPerUnit>
          <FungicidesPerUnit>0</FungicidesPerUnit>
          <InsecticidesPerUnit>0</InsecticidesPerUnit>
          <OtherPerUnit>0</OtherPerUnit>
          <TotalSpraysPerUnit>0</TotalSpraysPerUnit>
          <SundriesPerUnit>0</SundriesPerUnit>
          <TotalSeeds>0</TotalSeeds>
          <TotalFertiliser>0</TotalFertiliser>
          <TotalHerbicides>0</TotalHerbicides>
          <TotalFungicides>0</TotalFungicides>
          <TotalInsecticides>0</TotalInsecticides>
          <TotalOther>0</TotalOther>
          <TotalSpraysTotal>0</TotalSpraysTotal>
          <TotalSundries>0</TotalSundries>
        </VariableCosts>
        <DistributionPerUnitLabour>0</DistributionPerUnitLabour>
        <DistributionTotalLabour>0</DistributionTotalLabour>
        <DistributionPerUnitMachinery>0</DistributionPerUnitMachinery>
        <DistributionTotalMachinery>0</DistributionTotalMachinery>
        <DistributionPerUnitLabourW>0</DistributionPerUnitLabourW>
        <DistributionTotalLabourW>0</DistributionTotalLabourW>
        <DistributionPerUnitMachineryW>0</DistributionPerUnitMachineryW>
        <DistributionTotalMachineryW>0</DistributionTotalMachineryW>
        <TotalPerUnitCost>0</TotalPerUnitCost>
        <TotalTotalCost>0</TotalTotalCost>
        <TotalCostPerTonne>0</TotalCostPerTonne>
      </Crop>
      <Crop xmlns="http://www.helloFriend.com">
        <Name>Oats</Name>
        <UnitValue>0</UnitValue>
        <TonnesPerUnit>0</TonnesPerUnit>
        <TotalTonnes>0</TotalTonnes>
        <VariableCosts>
          <SeedsPerUnit>0</SeedsPerUnit>
          <FertiliserPerUnit>0</FertiliserPerUnit>
          <HerbicidesPerUnit>0</HerbicidesPerUnit>
          <FungicidesPerUnit>0</FungicidesPerUnit>
          <InsecticidesPerUnit>0</InsecticidesPerUnit>
          <OtherPerUnit>0</OtherPerUnit>
          <TotalSpraysPerUnit>0</TotalSpraysPerUnit>
          <SundriesPerUnit>0</SundriesPerUnit>
          <TotalSeeds>0</TotalSeeds>
          <TotalFertiliser>0</TotalFertiliser>
          <TotalHerbicides>0</TotalHerbicides>
          <TotalFungicides>0</TotalFungicides>
          <TotalInsecticides>0</TotalInsecticides>
          <TotalOther>0</TotalOther>
          <TotalSpraysTotal>0</TotalSpraysTotal>
          <TotalSundries>0</TotalSundries>
        </VariableCosts>
        <DistributionPerUnitLabour>0</DistributionPerUnitLabour>
        <DistributionTotalLabour>0</DistributionTotalLabour>
        <DistributionPerUnitMachinery>0</DistributionPerUnitMachinery>
        <DistributionTotalMachinery>0</DistributionTotalMachinery>
        <DistributionPerUnitLabourW>0</DistributionPerUnitLabourW>
        <DistributionTotalLabourW>0</DistributionTotalLabourW>
        <DistributionPerUnitMachineryW>0</DistributionPerUnitMachineryW>
        <DistributionTotalMachineryW>0</DistributionTotalMachineryW>
        <TotalPerUnitCost>0</TotalPerUnitCost>
        <TotalTotalCost>0</TotalTotalCost>
        <TotalCostPerTonne>0</TotalCostPerTonne>
      </Crop>
      <Crop xmlns="http://www.helloFriend.com">
        <Name>Linseed</Name>
        <UnitValue>0</UnitValue>
        <TonnesPerUnit>0</TonnesPerUnit>
        <TotalTonnes>0</TotalTonnes>
        <VariableCosts>
          <SeedsPerUnit>0</SeedsPerUnit>
          <FertiliserPerUnit>0</FertiliserPerUnit>
          <HerbicidesPerUnit>0</HerbicidesPerUnit>
          <FungicidesPerUnit>0</FungicidesPerUnit>
          <InsecticidesPerUnit>0</InsecticidesPerUnit>
          <OtherPerUnit>0</OtherPerUnit>
          <TotalSpraysPerUnit>0</TotalSpraysPerUnit>
          <SundriesPerUnit>0</SundriesPerUnit>
          <TotalSeeds>0</TotalSeeds>
          <TotalFertiliser>0</TotalFertiliser>
          <TotalHerbicides>0</TotalHerbicides>
          <TotalFungicides>0</TotalFungicides>
          <TotalInsecticides>0</TotalInsecticides>
          <TotalOther>0</TotalOther>
          <TotalSpraysTotal>0</TotalSpraysTotal>
          <TotalSundries>0</TotalSundries>
        </VariableCosts>
        <DistributionPerUnitLabour>0</DistributionPerUnitLabour>
        <DistributionTotalLabour>0</DistributionTotalLabour>
        <DistributionPerUnitMachinery>0</DistributionPerUnitMachinery>
        <DistributionTotalMachinery>0</DistributionTotalMachinery>
        <DistributionPerUnitLabourW>0</DistributionPerUnitLabourW>
        <DistributionTotalLabourW>0</DistributionTotalLabourW>
        <DistributionPerUnitMachineryW>0</DistributionPerUnitMachineryW>
        <DistributionTotalMachineryW>0</DistributionTotalMachineryW>
        <TotalPerUnitCost>0</TotalPerUnitCost>
        <TotalTotalCost>0</TotalTotalCost>
        <TotalCostPerTonne>0</TotalCostPerTonne>
      </Crop>
    </Crops>
    <Overheads>
      <FullTimeLabour>0</FullTimeLabour>
      <FullTimeLabourCC>0</FullTimeLabourCC>
      <FullTimeLabourCCPercent>0</FullTimeLabourCCPercent>
      <PartTimeLabour>0</PartTimeLabour>
      <PartTimeLabourCC>0</PartTimeLabourCC>
      <PartTimeLabourCCPercent>0</PartTimeLabourCCPercent>
      <NumberOfPropriators>0</NumberOfPropriators>
      <NotionalLabourCost>0</NotionalLabourCost>
      <NotionalLabourCostCC>0</NotionalLabourCostCC>
      <NotionalLabourCostCCPercent>0</NotionalLabourCostCCPercent>
      <TotalLabourCost>0</TotalLabourCost>
      <TotalLabourCostCC>0</TotalLabourCostCC>
      <OtherLabourCosts />
      <MachineryDepn>0</MachineryDepn>
      <MachineryRepairs>0</MachineryRepairs>
      <MachineryRepairsCC>0</MachineryRepairsCC>
      <MachineryRepairsCCPercent>0</MachineryRepairsCCPercent>
      <VehicleRepairs>0</VehicleRepairs>
      <VehicleRepairsCC>0</VehicleRepairsCC>
      <VehicleRepairsCCPercent>0</VehicleRepairsCCPercent>
      <MachineryFuel>0</MachineryFuel>
      <MachineryFuelCC>0</MachineryFuelCC>
      <MachineryFuelCCPercent>0</MachineryFuelCCPercent>
      <VehicleFuel>0</VehicleFuel>
      <VehicleFuelCC>0</VehicleFuelCC>
      <VehicleFuelCCPercent>0</VehicleFuelCCPercent>
      <Haulage>0</Haulage>
      <HaulageCC>0</HaulageCC>
      <HaulageCCPercent>0</HaulageCCPercent>
      <GrainStorage>0</GrainStorage>
      <GrainStorageCC>0</GrainStorageCC>
      <GrainStorageCCPercent>0</GrainStorageCCPercent>
      <ElectricAndGas>0</ElectricAndGas>
      <ElectricAndGasCC>0</ElectricAndGasCC>
      <ElectricAndGasCCPercent>0</ElectricAndGasCCPercent>
      <ContractStaff>0</ContractStaff>
      <ContractStaffCC>0</ContractStaffCC>
      <ContractStaffCCPercent>0</ContractStaffCCPercent>
      <TotalMachineryCost>0</TotalMachineryCost>
      <TotalMachineryCostCC>0</TotalMachineryCostCC>
      <OtherMachineryCosts />
      <MachineryDepreciation>
        <CurrentYear>0</CurrentYear>
        <Expanded>false</Expanded>
        <Machines>
          <DepreciationBase>
            <Index>0</Index>
            <Description>hello</Description>
            <PurchasedValue>2000</PurchasedValue>
            <PurchasedYear>1998</PurchasedYear>
            <DepnRatePercent>0.2</DepnRatePercent>
            <StartOfYearValue>419.43</StartOfYearValue>
            <CurrentYearDepn>83.89</CurrentYearDepn>
            <EndOfYearValue>335.54</EndOfYearValue>
            <CcDepnPercent>0.1</CcDepnPercent>
            <CurrentYearCCDepn>8.39</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>37.75</CcCurrentYearAverageCapital>
          </DepreciationBase>
          <DepreciationBase xsi:nil="true" />
          <DepreciationBase xsi:nil="true" />
          <DepreciationBase xsi:nil="true" />
        </Machines>
      </MachineryDepreciation>
      <PropertyDepn>0</PropertyDepn>
      <Repairs>0</Repairs>
      <RepairsCC>0</RepairsCC>
      <RepairsCCPercent>0</RepairsCCPercent>
      <CouncilTax>0</CouncilTax>
      <CouncilTaxCC>0</CouncilTaxCC>
      <CouncilTaxCCPercent>0</CouncilTaxCCPercent>
      <WaterRates>0</WaterRates>
      <WaterRatesCC>0</WaterRatesCC>
      <WaterRatesCCPercent>0</WaterRatesCCPercent>
      <Drainage>0</Drainage>
      <DrainageCC>0</DrainageCC>
      <DrainageCCPercent>0</DrainageCCPercent>
      <Lime>0</Lime>
      <LimeCC>0</LimeCC>
      <LimeCCPercent>0</LimeCCPercent>
      <PestControl>0</PestControl>
      <PestControlCC>0</PestControlCC>
      <PestControlCCPercent>0</PestControlCCPercent>
      <TotalPropertyCost>0</TotalPropertyCost>
      <TotalPropertyCostCC>0</TotalPropertyCostCC>
      <OtherPropertyCosts />
      <PropertyDepreciation>
        <CurrentYear>0</CurrentYear>
        <Expanded>false</Expanded>
        <Properties>
          <DepreciationBase>
            <Index>0</Index>
            <Description>House</Description>
            <PurchasedValue>10000</PurchasedValue>
            <PurchasedYear>2001</PurchasedYear>
            <DepnRatePercent>0.11</DepnRatePercent>
            <StartOfYearValue>6274.22</StartOfYearValue>
            <CurrentYearDepn>690.16</CurrentYearDepn>
            <EndOfYearValue>5584.06</EndOfYearValue>
            <CcDepnPercent>0.12</CcDepnPercent>
            <CurrentYearCCDepn>82.82</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>711.50</CcCurrentYearAverageCapital>
          </DepreciationBase>
        </Properties>
      </PropertyDepreciation>
      <InsuranceCosts>0</InsuranceCosts>
      <InsuranceCostsCC>0</InsuranceCostsCC>
      <InsuranceCostsCCPercent>0</InsuranceCostsCCPercent>
      <ProfessionalFees>0</ProfessionalFees>
      <ProfessionalFeesCC>0</ProfessionalFeesCC>
      <ProfessionalFeesCCPercent>0</ProfessionalFeesCCPercent>
      <OtherAdminCosts>0</OtherAdminCosts>
      <OtherAdminCostsCC>0</OtherAdminCostsCC>
      <OtherAdminCostsCCPercent>0</OtherAdminCostsCCPercent>
      <Subscriptions>0</Subscriptions>
      <SubscriptionsCC>0</SubscriptionsCC>
      <SubscriptionsCCPercent>0</SubscriptionsCCPercent>
      <Miscellaneous>0</Miscellaneous>
      <MiscellaneousCC>0</MiscellaneousCC>
      <MiscellaneousCCPercent>0</MiscellaneousCCPercent>
      <SecretarialSupport>0</SecretarialSupport>
      <SecretarialSupportCC>0</SecretarialSupportCC>
      <SecretarialSupportCCPercent>0</SecretarialSupportCCPercent>
      <TotalAdminCost>0</TotalAdminCost>
      <TotalAdminCostCC>0</TotalAdminCostCC>
      <OtherAdministrationCosts />
      <AhaTenancies>0</AhaTenancies>
      <AhaTenanciesCC>0</AhaTenanciesCC>
      <AhaTenanciesCCPercent>0</AhaTenanciesCCPercent>
      <FarmTenancies>0</FarmTenancies>
      <FarmTenanciesCC>0</FarmTenanciesCC>
      <FarmTenanciesCCPercent>0</FarmTenanciesCCPercent>
      <NoRentArea>0</NoRentArea>
      <NoRentAreaRent>0</NoRentAreaRent>
      <NotionalRentCC>0</NotionalRentCC>
      <NotionalRentCCPercent>0</NotionalRentCCPercent>
      <TotalRentCost>0</TotalRentCost>
      <TotalRentCostCC>0</TotalRentCostCC>
      <OtherRentCosts />
      <OverdraftInterest>0</OverdraftInterest>
      <OverdraftInterestCC>0</OverdraftInterestCC>
      <OverdraftInterestCCPercent>0</OverdraftInterestCCPercent>
      <BankCharges>0</BankCharges>
      <BankChargesCC>0</BankChargesCC>
      <BankChargesCCPercent>0</BankChargesCCPercent>
      <LoanInterest>0</LoanInterest>
      <LoanInterestCC>0</LoanInterestCC>
      <LoanInterestCCPercent>0</LoanInterestCCPercent>
      <HpInterest>0</HpInterest>
      <HpInterestCC>0</HpInterestCC>
      <HpInterestCCPercent>0</HpInterestCCPercent>
      <TotalFinanceCostCC>0</TotalFinanceCostCC>
      <OtherFinanceCosts />
    </Overheads>
    <AreaMeasurement>Acre</AreaMeasurement>
    <FarmName>Hello3</FarmName>
    <Region>South East</Region>
    <FarmType>Organic</FarmType>
    <UseOfData>Actual</UseOfData>
    <TotalCropArea>500</TotalCropArea>
    <TotalSeedCost>0</TotalSeedCost>
    <TotalFertiliserCost>0</TotalFertiliserCost>
    <TotalHerbicidesCost>0</TotalHerbicidesCost>
    <TotalFungicidesCost>0</TotalFungicidesCost>
    <TotalInsecticidesCost>0</TotalInsecticidesCost>
    <TotalOtherSpraysCost>0</TotalOtherSpraysCost>
    <TotalSpraysCost>0</TotalSpraysCost>
    <TotalSundriesCost>0</TotalSundriesCost>
    <TotalVariableCost>0</TotalVariableCost>
  </FarmDetails>
  <HarvestYear>2005</HarvestYear>
  <GroupID>1</GroupID>
  <GroupName>Hassan's</GroupName>
  <GroupDesc>Test group for Hassan 8</GroupDesc>
  <HasBeenSubmitted>false</HasBeenSubmitted>
  <GrossMarginSubmitted>false</GrossMarginSubmitted>
  <IsNew>false</IsNew>
  <CreatedDate>13 October 2006 16:28</CreatedDate>
  <SavedDate>26 October 2006 15:16</SavedDate>
  <SaveFile>C:\Program Files\helloFriend\Benchmark\UserData\helloFriend_benchmark_128052269206236220.Xml</SaveFile>
  <Comments>My Comment 2</Comments>
  <CopyText>Copy of Copy of </CopyText>
</Benchmark>


==== strored procedure ====

ALTER Procedure bm_registeredData_load_xml
(
      -- Benchmark Xml.
      @xmlDoc ntext = null,
      @userId int = null
)
AS
      SET NOCOUNT ON
      DECLARE @idoc int, @bmId int, @year int

      SET @bmId = 0

      -- Check we have some data to work with.
      IF @xmlDoc IS NOT NULL
      BEGIN
            EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc, '<root xmlns:ns="http://www.hgca.com"/>'

            -- Add to bm_registeredData
            INSERT INTO
                  bm_registeredData
            SELECT
                  @userId AS userId, HarvestYear, GroupID, CreatedDate, SavedDate, Comments, DateUploaded, Valid
            FROM
                  OPENXML (@idoc, '/*', 2) WITH bm_registeredData            

            -- Store the identy ID.
            SELECT @bmId = @@IDENTITY

            -- Add to bm_farmDetails
            INSERT INTO
                  bm_farmDetails
            SELECT
                  @bmId AS bmId, AreaMeasurement, FarmName, Region, FarmType, UseOfData, TotalCropArea, TotalSeedCost, TotalFertiliserCost,
                  TotalHerbicidesCost, TotalFungicidesCost, TotalInsecticidesCost, TotalOtherSpraysCost, TotalSpraysCost, TotalSundriesCost, TotalVariableCost
            FROM
                  OPENXML (@idoc, '/Benchmark/FarmDetails', 2) WITH bm_farmDetails

            -- Add to bm_crops
            INSERT INTO
                  bm_crops
            SELECT
                  @bmId AS bmId, [Name], UnitValue, TonnesPerUnit, TotalTonnes, DistributionPerUnitLabour, DistributionTotalLabour,
                  DistributionPerUnitMachinery, DistributionTotalMachinery, DistributionPerUnitLabourW, DistributionTotalLabourW,
                  DistributionPerUnitMachineryW, DistributionTotalMachineryW, TotalPerUnitCost, TotalTotalCost, TotalCostPerTonne,
                  SeedsPerUnit, FertiliserPerUnit, HerbicidesPerUnit, FungicidesPerUnit, InsecticidesPerUnit, OtherPerUnit,
                  TotalSpraysPerUnit, SundriesPerUnit, TotalSeeds, TotalFertiliser, TotalHerbicides, TotalFungicides,
                  TotalInsecticides, TotalOther, TotalSpraysTotal, TotalSundries                  
            FROM
                  OPENXML (@idoc, 'Crops/ns:Crop', 2)
        WITH ([Name]      varchar      (100)                                    'ns:Name',
                        UnitValue      numeric                                          'ns:UnitValue',
                        TonnesPerUnit      numeric                                    'ns:TonnesPerUnit',
                        TotalTonnes      numeric                                          'ns:TotalTonnes',
                        DistributionPerUnitLabour      numeric                  'ns:DistributionPerUnitLabour',
                        DistributionTotalLabour      numeric                        'ns:DistributionTotalLabour',
                        DistributionPerUnitMachinery      numeric            'ns:DistributionPerUnitMachinery',
                        DistributionTotalMachinery      numeric                  'ns:DistributionTotalMachinery',
                        DistributionPerUnitLabourW      numeric                  'ns:DistributionPerUnitLabourW',
                        DistributionTotalLabourW      numeric                  'ns:DistributionTotalLabourW',
                        DistributionPerUnitMachineryW      numeric            'ns:DistributionPerUnitMachineryW',
                        DistributionTotalMachineryW      numeric                  'ns:DistributionTotalMachineryW',
                        TotalPerUnitCost      numeric                              'ns:TotalPerUnitCost',
                        TotalTotalCost      numeric                                    'ns:TotalTotalCost',
                        TotalCostPerTonne      numeric                              'ns:TotalCostPerTonne',
                        SeedsPerUnit      numeric                                    'ns:VariableCosts/ns:SeedsPerUnit',
                        FertiliserPerUnit      numeric                              'ns:VariableCosts/ns:FertiliserPerUnit',
                        HerbicidesPerUnit      numeric                              'ns:VariableCosts/ns:HerbicidesPerUnit',
                        FungicidesPerUnit      numeric                              'ns:VariableCosts/ns:FungicidesPerUnit',
                        InsecticidesPerUnit      numeric                              'ns:VariableCosts/ns:InsecticidesPerUnit',
                        OtherPerUnit      numeric                                    'ns:VariableCosts/ns:OtherPerUnit',
                        TotalSpraysPerUnit      numeric                              'ns:VariableCosts/ns:TotalSpraysPerUnit',
                        SundriesPerUnit      numeric                                    'ns:VariableCosts/ns:SundriesPerUnit',
                        TotalSeeds      numeric                                          'ns:VariableCosts/ns:TotalSeeds',
                        TotalFertiliser      numeric                                    'ns:VariableCosts/ns:TotalFertiliser',
                        TotalHerbicides      numeric                                    'ns:VariableCosts/ns:TotalHerbicides',
                        TotalFungicides      numeric                                    'ns:VariableCosts/ns:TotalFungicides',
                        TotalInsecticides      numeric                              'ns:VariableCosts/ns:TotalInsecticides',
                        TotalOther      numeric                                          'ns:VariableCosts/ns:TotalOther',
                        TotalSpraysTotal      numeric                              'ns:VariableCosts/ns:TotalSpraysTotal',
                        TotalSundries      numeric                                    'ns:VariableCosts/ns:TotalSundries')
                  


            -- Release resources.
            EXEC sp_xml_removedocument @idoc

      END

      -- Return the record identity.
      RETURN @bmId


 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17812366
Post the structure of your bm_registeredData and bm_farmDetails in a CREATE TABLE statement so that I can test out.

P.S. It is always best to explicitly name all your columns in an INSERT statement as in
INSERT TableName(Col1, Col2, ...)
Select   ColA, ColB, ...
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17812626
OK,

CREATE TABLE [bm_registeredData] (
      [bmId] [int] IDENTITY (1, 1) NOT NULL ,
      [UserId] [int] NOT NULL ,
      [HarvestYear] [int] NOT NULL ,
      [GroupID] [int] NULL ,
      [CreatedDate] [datetime] NULL ,
      [SavedDate] [datetime] NULL ,
      [Comments] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [DateUploaded] [datetime] NULL ,
      [Valid] [tinyint] NULL ,
      CONSTRAINT [PK_bm_registeredData] PRIMARY KEY  CLUSTERED
      (
            [bmId]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE [bm_farmDetails] (
      [bmId] [int] NOT NULL ,
      [AreaMeasurement] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [FarmName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Region] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [FarmType] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [UseOfData] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TotalCropArea] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalCropArea] DEFAULT (0),
      [TotalSeedCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalSeedCost] DEFAULT (0),
      [TotalFertiliserCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalFertiliserCost] DEFAULT (0),
      [TotalHerbicidesCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalHerbicidesCost] DEFAULT (0),
      [TotalFungicidesCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalFungicidesCost] DEFAULT (0),
      [TotalInsecticidesCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalInsecticidesCost] DEFAULT (0),
      [TotalOtherSpraysCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalOtherSpraysCost] DEFAULT (0),
      [TotalSpraysCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalSpraysCost] DEFAULT (0),
      [TotalSundriesCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalSundriesCost] DEFAULT (0),
      [TotalVariableCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_farmDetails_TotalVariableCost] DEFAULT (0)
) ON [PRIMARY]
GO


Explicitly naming cols.. will do for smaller tables.  I have a couple more other tables which are huge.. too much typing ;-).  would you say it is essential to do that way or just a bit safer etc?

Thanks for your help
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17813819
>>would you say it is essential to do that way or just a bit safer etc?<<
I would say both, others would settle for safer :)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Author Comment

by:gbzhhu
ID: 17813965
Cool, I will take your advice and do some typing.

Regarding your test for the bm_registeredData and bm_farmDetails, these tables already get the correct data inserted by the sproc, I am just stuck with bm_crops table.  Did you find any descrepencies? anything suspicious?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17814283
>>Cool, I will take your advice and do some typing.<<
You don't have to.  Using SQL Query Analyzer you can just use the mouse:
1. Go to the Object Browser.
2. Find your database and expand it.
3. Find the table and expand it
4. Select columns and drag it on to the main SQL Query Analyzer window.

It will list all the column names.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17814298
>>I am just stuck with bm_crops table. <<
You are right, I overlooked that.  Please post the CREATE TABLE for bm_crops.
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17814305
Cheers for that.  Didn't know that either!!
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17814313
Here

CREATE TABLE [bm_crops] (
      [cropId] [int] IDENTITY (1, 1) NOT NULL ,
      [bmId] [int] NOT NULL ,
      [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [UnitValue] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_unitValue] DEFAULT (0),
      [TonnesPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_tonnesPerUnit] DEFAULT (0),
      [TotalTonnes] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_totalTonnes] DEFAULT (0),
      [DistributionPerUnitLabour] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionPerUnitLabour] DEFAULT (0),
      [DistributionTotalLabour] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionTotalLabour] DEFAULT (0),
      [DistributionPerUnitMachinery] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionPerUnitMachinery] DEFAULT (0),
      [DistributionTotalMachinery] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionTotalMachinery] DEFAULT (0),
      [DistributionPerUnitLabourW] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionPerUnitLabourW] DEFAULT (0),
      [DistributionTotalLabourW] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionTotalLabourW] DEFAULT (0),
      [DistributionPerUnitMachineryW] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionPerUnitMachineryW] DEFAULT (0),
      [DistributionTotalMachineryW] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_distributionTotalMachineryW] DEFAULT (0),
      [TotalPerUnitCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_totalPerUnitCost] DEFAULT (0),
      [TotalTotalCost] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_totalTotalCost] DEFAULT (0),
      [TotalCostPerTonne] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_totalCostPerTonne] DEFAULT (0),
      [SeedsPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_SeedsPerUnit_1] DEFAULT (0),
      [FertiliserPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_FertiliserPerUnit_1] DEFAULT (0),
      [HerbicidesPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_HerbicidesPerUnit_1] DEFAULT (0),
      [FungicidesPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_FungicidesPerUnit_1] DEFAULT (0),
      [InsecticidesPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_InsecticidesPerUnit_1] DEFAULT (0),
      [OtherPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_OtherPerUnit_1] DEFAULT (0),
      [TotalSpraysPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalSpraysPerUnit_1] DEFAULT (0),
      [SundriesPerUnit] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_SundriesPerUnit_1] DEFAULT (0),
      [TotalSeeds] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalSeeds_1] DEFAULT (0),
      [TotalFertiliser] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalFertiliser_1] DEFAULT (0),
      [TotalHerbicides] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalHerbicides_1] DEFAULT (0),
      [TotalFungicides] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalFungicides_1] DEFAULT (0),
      [TotalInsecticides] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalInsecticides_1] DEFAULT (0),
      [TotalOther] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalOther_1] DEFAULT (0),
      [TotalSpraysTotal] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalSpraysTotal_1] DEFAULT (0),
      [TotalSundries] [numeric](18, 0) NULL CONSTRAINT [DF_bm_crops_TotalSundries_1] DEFAULT (0)
) ON [PRIMARY]
GO
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17814321
When the sproc is run nothing appears on the table, no nulls or zeros.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17814684
If you Xml now includes:
<Benchmark xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <FarmDetails>

You will have to change the following line:
              OPENXML (@idoc, 'Crops/ns:Crop', 2)

You may also have to change the WITH clause elements, as well as modify the sp_xml_preparedocument to account for the additional namespaces.
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17815906
OK.  Any idea what I may have to change these to?

I could take the namespace off the Crops element.  I don't necessarily need it.  Should that fix it?  Will try it tomorrow, I am home now.  Bloody namespaces, gave me more grieve than I had bargained for!!!

Cheers
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 17816581
No need to include the xsd and xsi namespaces.  I was wrong about that.

All you have to do is:

Change:
      EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc, '<root xmlns:ns="http://www.hgca.com"/>'

To:
      EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDoc, '<root xmlns:ns="http://www.helloFriend.com"/>'

And

Change:
      OPENXML (@idoc, 'Crops/ns:Crop', 2)
To:

      OPENXML (@idoc, 'Benchmark/FarmDetails/Crops/ns:Crop', 2)

to match the Xml document you posted.
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17818009
YES!!!  :-)

the namespace <root xmlns:ns="http://www.hgca.com"/> was a copy and paste mistake, so wasn't the issue.  The fix was the change to the OPENXML.  All is working superbly now.  You deserve A++ for your efforts and genuine helpful attitude.  Thank you.

I have learnt quite a lot and so will carry on trying to populate the rest of the tables.  If I run into any trouble I will create another question.

Cheers
0
 
LVL 12

Author Comment

by:gbzhhu
ID: 17818782
Hi acperkins,

I run into a problem and created a new question, could you have a look at it please

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22039792.html

Thanks
H
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

14 Experts available now in Live!

Get 1:1 Help Now