CODEWITHSUNDEEP
sqlsql-serverxml
akaWizzmaster
akaWizzmaster

Reputation: 103

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!

Upvotes: 0

Views: 62

Answers (2)

Zee
Zee

Reputation: 840

What percent element type? you try to convert it to DECIMAL(17,2) in your select; but in your where clause, it is INT.

The error complains your Nullif default value. Sql cannot convert a '' to a decimal.

Fix:

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)'), '0') ,
                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]', 'DECIMAL(17,2)') > 0

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You are setting the empty string here

... = NULLIF(something,'')

The empty string is not valid in an numeric context. The types must be the same for expression1 and expression2: https://msdn.microsoft.com/en-us/library/ms177562.aspx

Try it like this:

SELECT  targetId = NULLIF(cb.n.value('(targetId)[1]', 'INT'),
                            0) ,
        categoryId = NULLIF(cb.n.value('(categoryId)[1]',
                                        'SMALLINT'), 0) ,
        percentage = NULLIF(cb.n.value('(percent)[1]',
                                        'DECIMAL(17,2)'), 0) ,
        AgreementDurationMin = ISNULL(NULLIF(cb.n.value('(AgreementDurationMin)[1]',
                                                    'INT'), 0), 0) ,
        AgreementDurationMax = NULLIF(cb.n.value('(AgreementDurationMax)[1]',
                                                    'INT'), 0)
FROM    @XmlIn.nodes('rec') cb ( n )
WHERE   cb.n.value('(percent/text())[1]', 'INT') > 0

Upvotes: 1

Related Questions