I am Reading an XML file from a system and I want to update a table with the values from XML file. All my values in XML file are nvarchar but my table columns contains datatypes of both nvarchar and real. Is there any smart way to cast only the values the are needed. Hope you understand what I want to do.
My update query.
SELECT @stålsort=stålsort, @ytterdiameter=ytterdiameter, @väggdiameter=väggdiameter, @dimension=dimension
@senast_sparat=senast_sparat, @anvandare=anvandare FROM OPENXML (@imessagebody, @messageXPath)
Thank you four your response!
Yes god question could we make a script that ask the table what datatype is in column1, column2 and so on?
Otherwise I ofcourse know hon my database table looks like.
I send a part of the create script if it help.
Hope you understand me.
You could use sql_variant at the input type, then test it for numeric data (but don't use ISNUMERIC, that's unreliable) The SQL_VARIANT_PROPERTY function may help you here, but not for data just imported.
What I would do though is use an SSIS solution. Define the input column as text. Use a script compoment to do a float.TryParse on it to see if it is really a float. Then create two derived columns. one for text and one for the floats. The result table would have two columns where you have one on the input xml.
Cant I do a cast in my update query?
stålsort are nvarchar in my table the other are real in my table but every local variable @xxxx
are nvarchar i guess the problem is when I try to update a table value who is real with an nvarchar.
UPDATE Recept_215_Ugn1
SET stålsort=@stålsort, ytterdiameter=CAST(@ytterdiameter) as real, väggdiameter=CAST(@väggdiameter) as real,
WHERE tube_info=@tube_info