XML : XML Conversion issue

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