CODEWITHSUNDEEP
sql-serverxml-parsing
Anuja
Anuja

Reputation: 59

Conversion failed when converting the nvarchar value' ' to data type int in Xml Parsing

DECLARE @xml xml ='<Root><Child><Number>
80</Number></Child></Root>'

SELECT  c.value('(./Number)[1]', 'int') AS Number  FROM @xml.nodes('Root/Child') T(c)

I am getting the xml from the file, so if the value of the node ( here it is 80 )is in the new line ,on selecting the xml using the above query I got an error like:

Msg 245, Level 16, State 1, Line 4 Conversion failed when converting the nvarchar value '
80' to data type int.

But If the xml is declare in the same line like DECLARE @xml xml ='<Root><Child><Number>80</Number></Child></Root>'

It will work fine. How could I solve this issue . Anybody please help me to proceed

Upvotes: 2

Views: 1953

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Try using the number function:

DECLARE @xml xml ='<Root><Child><Number>
80</Number></Child></Root>'

SELECT  c.value('number((./Number)[1])', 'int') AS Number
FROM @xml.nodes('Root/Child') T(c)

Upvotes: 3

Related Questions