Getting the following error; "Error converting data type varchar to numeric".
This happens when trying to select data from an XML input. Below is the code:
DECLARE @XmlIn XML = '<rec> <targetId>10</targetId> <categoryId>4</categoryId> <percent>2</percent> <AgreementDurationMin></AgreementDurationMin> <AgreementDurationMax></AgreementDurationMax> </rec>' INSERT INTO WizzTable ( CommissionTargetId , CommissionPercentageCategoryId , Percentage , AgreementDurationMin , AgreementDurationMax ) SELECT targetId = NULLIF(cb.n.value('(targetId)[1]', 'INT'), '') , categoryId = NULLIF(cb.n.value('(categoryId)[1]', 'SMALLINT'), '') , percentage = NULLIF(cb.n.value('(percent)[1]', 'DECIMAL(17,2)'), '') , AgreementDurationMin = ISNULL(NULLIF(cb.n.value('(AgreementDurationMin)[1]', 'INT'), ''), 0) , AgreementDurationMax = NULLIF(cb.n.value('(AgreementDurationMax)[1]', 'INT'), '') FROM @XmlIn.nodes('rec') cb ( n ) WHERE cb.n.value('(percent/text())[1]', 'INT') > 0 Any ideas? Thanks!
No comments:
Post a Comment