• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1284
  • Last Modified:

error converting data type nvarchar to numeric

Hi,

I am writing some XML data to tables  using OPENXML.  I have a problem writing to 1 table.  The error I get is 'error converting data type nvarchar to numeric'.  There is no nvarchar column in the table, only 1 int column and the rest are all numeric (18, 0).  below is the stored procedure block that attempts to write to my table (bm_overheads) and the XML element holding the data.  I hope someone can figure out where the error is coming from.

====== sproc block =========

            -- Add to bm_overheads
            INSERT INTO
                  bm_overheads
            SELECT
                  @bmId AS bmId, FullTimeLabour, FullTimeLabourCC, FullTimeLabourCCPercent, PartTimeLabour, PartTimeLabourCC, PartTimeLabourCCPercent,
                  NumberOfPropriators, NotionalLabourCost, NotionalLabourCostCC, NotionalLabourCostCCPercent, TotalLabourCost, TotalLabourCostCC,
                  OtherLabourCosts, MachineryDepn, MachineryRepairs, MachineryRepairsCC, MachineryRepairsCCPercent, VehicleRepairs, VehicleRepairsCC,
                  VehicleRepairsCCPercent, MachineryFuel, MachineryFuelCC, MachineryFuelCCPercent, VehicleFuel, VehicleFuelCC, VehicleFuelCCPercent,
                  Haulage, HaulageCC, HaulageCCPercent, GrainStorage, GrainStorageCC, GrainStorageCCPercent, ElectricAndGas, ElectricAndGasCC,
                  ElectricAndGasCCPercent, ContractStaff, ContractStaffCC, ContractStaffCCPercent, TotalMachineryCost, TotalMachineryCostCC,
                  OtherMachineryCosts, PropertyDepn, Repairs, RepairsCC, RepairsCCPercent, CouncilTax, CouncilTaxCC, CouncilTaxCCPercent, WaterRates,
                  WaterRatesCC, WaterRatesCCPercent, Drainage, DrainageCC, DrainageCCPercent, Lime, LimeCC, LimeCCPercent, PestControl, PestControlCC,
                  PestControlCCPercent, TotalPropertyCost, TotalPropertyCostCC, OtherPropertyCosts, InsuranceCosts, InsuranceCostsCC, InsuranceCostsCCPercent,
                  ProfessionalFees, ProfessionalFeesCC, ProfessionalFeesCCPercent, OtherAdminCosts, OtherAdminCostsCC, OtherAdminCostsCCPercent,
                  Subscriptions, SubscriptionsCC, SubscriptionsCCPercent, Miscellaneous, MiscellaneousCC, MiscellaneousCCPercent, SecretarialSupport,
                  SecretarialSupportCC, SecretarialSupportCCPercent, TotalAdminCost, TotalAdminCostCC, OtherAdministrationCosts, AhaTenancies, AhaTenanciesCC,
                  AhaTenanciesCCPercent, FarmTenancies, FarmTenanciesCC, FarmTenanciesCCPercent, NoRentArea, NoRentAreaRent, NotionalRentCC, NotionalRentCCPercent,
                  TotalRentCost, TotalRentCostCC, OtherRentCosts, OverdraftInterest, OverdraftInterestCC, OverdraftInterestCCPercent, BankCharges,
                  BankChargesCC, BankChargesCCPercent, LoanInterest, LoanInterestCC, LoanInterestCCPercent, HpInterest, HpInterestCC, HpInterestCCPercent,
                  TotalFinanceCostCC, OtherFinanceCosts
            FROM
                  OPENXML (@idoc, 'Benchmark/FarmDetails/Overheads', 2)
            WITH (FullTimeLabour       numeric (15, 2)                                          'FullTimeLabour',
                        FullTimeLabourCC       numeric (15, 2)                                    'FullTimeLabourCC',
                        FullTimeLabourCCPercent       numeric (15, 2)                        'FullTimeLabourCCPercent',
                        PartTimeLabour       numeric (15, 2)                                          'PartTimeLabour',
                        PartTimeLabourCC       numeric (15, 2)                                    'PartTimeLabourCC',
                        PartTimeLabourCCPercent      numeric (15, 2)                              'PartTimeLabourCCPercent',
                        NumberOfPropriators       numeric (15, 2)                              'NumberOfPropriators',
                        NotionalLabourCost       numeric (15, 2)                                    'NotionalLabourCost',
                        NotionalLabourCostCC       numeric (15, 2)                              'NotionalLabourCostCC',
                        NotionalLabourCostCCPercent       numeric (15, 2)                  'NotionalLabourCostCCPercent',
                        TotalLabourCost       numeric (15, 2)                                    'TotalLabourCost',
                        TotalLabourCostCC      numeric (15, 2)                                    'TotalLabourCostCC',
                        OtherLabourCosts       numeric (15, 2)                                    'OtherLabourCosts',
                        MachineryDepn       numeric (15, 2)                                          'MachineryDepn',
                        MachineryRepairs       numeric (15, 2)                                    'MachineryRepairs',
                        MachineryRepairsCC       numeric (15, 2)                                    'MachineryRepairsCC',
                        MachineryRepairsCCPercent       numeric (15, 2)                        'MachineryRepairsCCPercent',
                        VehicleRepairs       numeric (15, 2)                                          'VehicleRepairs',
                        VehicleRepairsCC       numeric (15, 2)                                    'VehicleRepairsCC',
                        VehicleRepairsCCPercent       numeric (15, 2)                        'VehicleRepairsCCPercent',
                        MachineryFuel       numeric (15, 2)                                          'MachineryFuel',
                        MachineryFuelCC       numeric (15, 2)                                    'MachineryFuelCC',
                        MachineryFuelCCPercent       numeric (15, 2)                              'MachineryFuelCCPercent',
                        VehicleFuel       numeric (15, 2)                                          'VehicleFuel',
                        VehicleFuelCC       numeric (15, 2)                                          'VehicleFuelCC',
                        VehicleFuelCCPercent       numeric (15, 2)                              'VehicleFuelCCPercent',
                        Haulage       numeric (15, 2)                                                'Haulage',
                        HaulageCC       numeric (15, 2)                                                'HaulageCC',
                        HaulageCCPercent       numeric (15, 2)                                    'HaulageCCPercent',
                        GrainStorage       numeric (15, 2)                                          'GrainStorage',
                        GrainStorageCC       numeric (15, 2)                                          'GrainStorageCC',
                        GrainStorageCCPercent       numeric (15, 2)                              'GrainStorageCCPercent',
                        ElectricAndGas       numeric (15, 2)                                          'ElectricAndGas',
                        ElectricAndGasCC       numeric (15, 2)                                    'ElectricAndGasCC',
                        ElectricAndGasCCPercent       numeric (15, 2)                        'ElectricAndGasCCPercent',
                        ContractStaff       numeric (15, 2)                                          'ContractStaff',
                        ContractStaffCC       numeric (15, 2)                                    'ContractStaffCC',
                        ContractStaffCCPercent       numeric (15, 2)                              'ContractStaffCCPercent',
                        TotalMachineryCost       numeric (15, 2)                                    'TotalMachineryCost',
                        TotalMachineryCostCC       numeric (15, 2)                              'TotalMachineryCostCC',
                        OtherMachineryCosts       numeric (15, 2)                              'OtherMachineryCosts',
                        PropertyDepn       numeric (15, 2)                                          'PropertyDepn',
                        Repairs       numeric (15, 2)                                                'Repairs',
                        RepairsCC       numeric (15, 2)                                                'RepairsCC',
                        RepairsCCPercent       numeric (15, 2)                                    'RepairsCCPercent',
                        CouncilTax       numeric (15, 2)                                                'CouncilTax',
                        CouncilTaxCC       numeric (15, 2)                                          'CouncilTaxCC',
                        CouncilTaxCCPercent       numeric (15, 2)                              'CouncilTaxCCPercent',
                        WaterRates       numeric (15, 2)                                                'WaterRates',
                        WaterRatesCC       numeric (15, 2)                                          'WaterRatesCC',
                        WaterRatesCCPercent       numeric (15, 2)                              'WaterRatesCCPercent',
                        Drainage       numeric (15, 2)                                                'Drainage',
                        DrainageCC       numeric (15, 2)                                                'DrainageCC',
                        DrainageCCPercent       numeric (15, 2)                                    'DrainageCCPercent',
                        Lime       numeric (15, 2)                                                      'Lime',
                        LimeCC       numeric (15, 2)                                                      'LimeCC',
                        LimeCCPercent       numeric (15, 2)                                          'LimeCCPercent',
                        PestControl       numeric (15, 2)                                          'PestControl',
                        PestControlCC       numeric (15, 2)                                          'PestControlCC',
                        PestControlCCPercent       numeric (15, 2)                              'PestControlCCPercent',
                        TotalPropertyCost       numeric (15, 2)                                    'TotalPropertyCost',
                        TotalPropertyCostCC       numeric (15, 2)                              'TotalPropertyCostCC',
                        OtherPropertyCosts       numeric (15, 2)                                    'OtherPropertyCosts',
                        InsuranceCosts       numeric (15, 2)                                          'InsuranceCosts',
                        InsuranceCostsCC       numeric (15, 2)                                    'InsuranceCostsCC',
                        InsuranceCostsCCPercent      numeric (15, 2)                              'InsuranceCostsCCPercent',
                        ProfessionalFees       numeric (15, 2)                                    'ProfessionalFees',
                        ProfessionalFeesCC       numeric (15, 2)                                    'ProfessionalFeesCC',
                        ProfessionalFeesCCPercent       numeric (15, 2)                        'ProfessionalFeesCCPercent',
                        OtherAdminCosts       numeric (15, 2)                                    'OtherAdminCosts',
                        OtherAdminCostsCC       numeric (15, 2)                                    'OtherAdminCostsCC',
                        OtherAdminCostsCCPercent       numeric (15, 2)                        'OtherAdminCostsCCPercent',
                        Subscriptions       numeric (15, 2)                                          'Subscriptions',
                        SubscriptionsCC       numeric (15, 2)                                    'SubscriptionsCC',
                        SubscriptionsCCPercent       numeric (15, 2)                              'SubscriptionsCCPercent',
                        Miscellaneous       numeric (15, 2)                                          'Miscellaneous',
                        MiscellaneousCC       numeric (15, 2)                                    'MiscellaneousCC',
                        MiscellaneousCCPercent       numeric (15, 2)                              'MiscellaneousCCPercent',
                        SecretarialSupport      numeric (15, 2)                                    'SecretarialSupport',
                        SecretarialSupportCC       numeric (15, 2)                              'SecretarialSupportCC',
                        SecretarialSupportCCPercent       numeric (15, 2)                  'SecretarialSupportCCPercent',
                        TotalAdminCost       numeric (15, 2)                                          'TotalAdminCost',
                        TotalAdminCostCC       numeric (15, 2)                                    'TotalAdminCostCC',
                        OtherAdministrationCosts       numeric (15, 2)                        'OtherAdministrationCosts',
                        AhaTenancies       numeric (15, 2)                                          'AhaTenancies',
                        AhaTenanciesCC      numeric (15, 2)                                          'AhaTenanciesCC',
                        AhaTenanciesCCPercent       numeric (15, 2)                              'AhaTenanciesCCPercent',
                        FarmTenancies       numeric (15, 2)                                          'FarmTenancies',
                        FarmTenanciesCC       numeric (15, 2)                                    'FarmTenanciesCC',
                        FarmTenanciesCCPercent       numeric (15, 2)                              'FarmTenanciesCCPercent',
                        NoRentArea       numeric (15, 2)                                                'NoRentArea',
                        NoRentAreaRent       numeric (15, 2)                                          'NoRentAreaRent',
                        NotionalRentCC       numeric (15, 2)                                          'NotionalRentCC',
                        NotionalRentCCPercent      numeric (15, 2)                              'NotionalRentCCPercent',
                        TotalRentCost       numeric (15, 2)                                          'TotalRentCost',
                        TotalRentCostCC       numeric (15, 2)                                    'TotalRentCostCC',
                        OtherRentCosts       numeric (15, 2)                                          'OtherRentCosts',
                        OverdraftInterest       numeric (15, 2)                                    'OverdraftInterest',
                        OverdraftInterestCC       numeric (15, 2)                              'OverdraftInterestCC',
                        OverdraftInterestCCPercent       numeric (15, 2)                        'OverdraftInterestCCPercent',
                        BankCharges      numeric (15, 2)                                                'BankCharges',
                        BankChargesCC       numeric (15, 2)                                          'BankChargesCC',
                        BankChargesCCPercent       numeric (15, 2)                              'BankChargesCCPercent',
                        LoanInterest       numeric (15, 2)                                          'LoanInterest',
                        LoanInterestCC       numeric (15, 2)                                          'LoanInterestCC',
                        LoanInterestCCPercent       numeric (15, 2)                              'LoanInterestCCPercent',
                        HpInterest       numeric (15, 2)                                                'HpInterest',
                        HpInterestCC       numeric (15, 2)                                          'HpInterestCC',
                        HpInterestCCPercent      numeric (15, 2)                                    'HpInterestCCPercent',
                        TotalFinanceCostCC       numeric (15, 2)                                    'TotalFinanceCostCC',
                        OtherFinanceCosts      numeric (15, 2)                                    'OtherFinanceCosts')


====== XML element =========

    <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>Car 1</Description>
            <PurchasedValue>2000.00</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>
            <Index>1</Index>
            <Description>Car 2</Description>
            <PurchasedValue>7800</PurchasedValue>
            <PurchasedYear>2003</PurchasedYear>
            <DepnRatePercent>0.12</DepnRatePercent>
            <StartOfYearValue>6040.32</StartOfYearValue>
            <CurrentYearDepn>724.84</CurrentYearDepn>
            <EndOfYearValue>5315.48</EndOfYearValue>
            <CcDepnPercent>0.04</CcDepnPercent>
            <CurrentYearCCDepn>28.99</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>227.12</CcCurrentYearAverageCapital>
          </DepreciationBase>
        </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>
          <DepreciationBase>
            <Index>1</Index>
            <Description>House 2</Description>
            <PurchasedValue>0</PurchasedValue>
            <PurchasedYear>0</PurchasedYear>
            <DepnRatePercent>0</DepnRatePercent>
            <StartOfYearValue>0</StartOfYearValue>
            <CurrentYearDepn>0</CurrentYearDepn>
            <EndOfYearValue>0</EndOfYearValue>
            <CcDepnPercent>0</CcDepnPercent>
            <CurrentYearCCDepn>0</CurrentYearCCDepn>
            <CcCurrentYearAverageCapital>0</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>
0
gbzhhu
Asked:
gbzhhu
2 Solutions
 
NightmanCTOCommented:
What value do you pass in from empty properties (e.g. OtherPropertyCosts)?

If you don't convert an empty string to numeric, you will probably get this error. populate with zero when this is missing.
0
 
PErduCommented:
you're trying to push a nvarchar value into a numeric field...
0
 
gbzhhuAuthor Commented:
PErdu

Error suggests that, but my data contains only numeric values

Nightman,

I am leaving empty properties empty, is this seen as string data?  I am putting default value for all column to 0 now and see what happens
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
gbzhhuAuthor Commented:
I have given default value of 0 to all columns and also changed the stored procedure for the columns that can be blank like this

In the select statement

OtherPropertyCosts = isnull(OtherPropertyCosts, 0)

instead of

OtherPropertyCosts

This has not solved the problem.

Other ideas please.

Thanks
H
0
 
gpompeCommented:
The problem is that you have empty tags like <OtherLabourCosts /> (there are more in the XML)  replaced by <OtherLabourCosts>0</OtherLabourCosts> and it worked

This is the XML I used:


<Benchmark>
      <FarmDetails>
            <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>0</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>0</OtherMachineryCosts>
                  <MachineryDepreciation>
                        <CurrentYear>0</CurrentYear>
                        <Expanded>false</Expanded>
                        <Machines>
                              <DepreciationBase>
                                    <Index>0</Index>
                                    <Description>Car 1</Description>
                                    <PurchasedValue>2000.00</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>
                                    <Index>1</Index>
                                    <Description>Car 2</Description>
                                    <PurchasedValue>7800</PurchasedValue>
                                    <PurchasedYear>2003</PurchasedYear>
                                    <DepnRatePercent>0.12</DepnRatePercent>
                                    <StartOfYearValue>6040.32</StartOfYearValue>
                                    <CurrentYearDepn>724.84</CurrentYearDepn>
                                    <EndOfYearValue>5315.48</EndOfYearValue>
                                    <CcDepnPercent>0.04</CcDepnPercent>
                                    <CurrentYearCCDepn>28.99</CurrentYearCCDepn>
                                    <CcCurrentYearAverageCapital>227.12</CcCurrentYearAverageCapital>
                              </DepreciationBase>
                        </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>0</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>
                              <DepreciationBase>
                                    <Index>1</Index>
                                    <Description>House 2</Description>
                                    <PurchasedValue>0</PurchasedValue>
                                    <PurchasedYear>0</PurchasedYear>
                                    <DepnRatePercent>0</DepnRatePercent>
                                    <StartOfYearValue>0</StartOfYearValue>
                                    <CurrentYearDepn>0</CurrentYearDepn>
                                    <EndOfYearValue>0</EndOfYearValue>
                                    <CcDepnPercent>0</CcDepnPercent>
                                    <CurrentYearCCDepn>0</CurrentYearCCDepn>
                                    <CcCurrentYearAverageCapital>0</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>0</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>0</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>0</OtherFinanceCosts>
            </Overheads>
      </FarmDetails>
</Benchmark>
0
 
gbzhhuAuthor Commented:
Ok,  This was suggested by Nightman earlier.  I haven't modified the XML, I tried the modify the stored procedure which didn't help.  I will try to modify m,y program that generates the XML to insert 0 if no values exist for an element

Thanks
0
 
gbzhhuAuthor Commented:
gpompe,

Yep, that worked.  It was a big mistake on my part.  Elements OtherLabourCosts, OtherMachineryCosts, ... OtherxxxxxxCosts are arrays!!!  They didn't have array elements (0 lenght).  I removed then from the stored procedure code.  I will have to deal with them separately, maybe write them to a seperate table and associate them with the bm_overheads table

Thank you for help.  Since Nightman suggested this before you but it didn't direct me to a solution, I will divide points between you 2.  2/3 to you and 1/3 to him

Thank you again both of you.

H
0
 
gpompeCommented:
In that case what you can do is read the XML as varchar and convert the values in the select clause

For instance

FullTimeLabour      Varchar(20)                                   'FullTimeLabour',

and in the select clause something like

convert(numeric(15,2),case when ISNUMERIC(FullTimeLabour)=1 then FullTimeLabour else '0' end)

because FullTimeLabour is returned as empty string.

I know is a lot of writing, but it is a solution
0
 
gbzhhuAuthor Commented:
gpompe,

Maybe you didn't understand what I meant.  All elements are numeric data except few that are named like OtherxxxCosts (where xxx = Labour or Machinery or Property etc).  These elements represent an array in my C# code.  When the array has values the XML portion that represents them looks like this

      <OtherLabourCosts>
        <OtherLabourCost">
          <CostName>    One</CostName>
          <CostValue>0</CostValue>
          <CostValueCC>0</CostValueCC>
          <CostValueCCPercent>0</CostValueCCPercent>
        </OtherLabourCost>
        <OtherLabourCost">
          <CostName>    Two</CostName>
          <CostValue>0</CostValue>
          <CostValueCC>0</CostValueCC>
          <CostValueCCPercent>0</CostValueCCPercent>
        </OtherLabourCost>
      </OtherLabourCosts>

      <OtherPropertyCosts>
        <OtherPropertyCost">
          <CostName>    House</CostName>
          <CostValue>0</CostValue>
          <CostValueCC>0</CostValueCC>
          <CostValueCCPercent>0</CostValueCCPercent>
        </OtherPropertyCost>
      </OtherPropertyCosts>

They all have the same structure.  The only way I can think of dealing with them is to write them all to one table which will have cost type (so I can tell if I am dealing with Labour costs or property costs etc) and ID that associates it with the parent row (bm_overheads) and the actual data columns.  I think that should work.  If you can think of an improvement/better soluton please let me know.

Thanks, your input is much appreciated
H



0
 
gpompeCommented:
I understand, but your problem is in SQL SERVER.

This the thing : When SQL SERVER reads the XML is trying to convert what it reads to Numeric(15,2) because you defined that.
So in this line, SQL SERVER reads an empty tag it fail to convert (implicit) it to numeric and that the error you get.

Now my approach is to overcome the problem with the implicit convertion is read the XML into varchar field (does not matter if there is a number or not).
So now you can read the XML, but the fields in your table (bm_overheads) are numeric. So you have to convert them.

If we try to convert them as is, it will fail again. To overcome this I am using the ISNUMERIC function (it receives a varchar and returns 1 if the value can be convert to a numeric value)

case when ISNUMERIC(FullTimeLabour)=1 then FullTimeLabour else '0' end means if SQL can convert the value of the tag into numeric value, do it. Else the value is 0

The convert function is to explicitly convert the value, but you can omitt it and the values will be converted during the insert.

With this approach you do not have to modify the XML, just Insert query and final result is the same.
0
 
gbzhhuAuthor Commented:
OK, I am with you.  However, I removed these columns from the table as I cannot represent an array data in one column.  I also removed them from the select in the stored procedure.  This works fine now.  It doesn't deal with these fields.  I am the thinking of writing another insert into another table that holds these each array element in a row.  will this work?

If I follow your suggestion, I believe the insert into bm_overheads will work ok.  bm_overheads originally had 1 field for each array which was a mistake.  It is not possible to insert the arrays into bm_overheads as I don't know how many array elements there will be at runtime.  I will have to deal with the array data insert seperate from single item data insert into bm_overheads.  I am not SQL savvy, so i maybe lost.

Cheers
H
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now