asked on

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>

SOLUTION

Nightman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PErdu

you're trying to push a nvarchar value into a numeric field...

gbzhhu

ASKER

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

gbzhhu

ASKER

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

ASKER CERTIFIED SOLUTION

gpompe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gbzhhu

ASKER

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

gbzhhu

ASKER

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

gpompe

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

gbzhhu

ASKER

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

gpompe

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.

gbzhhu

ASKER

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