Error while converting XML date data to SQL column data.

Gani_tpt
1,261
Reputation points
I am trying to insert XML date data to SQL table.
my XML field is "DateRelieved" and its XML element value is "2022-02-25T00:00:00.000Z"
My SQL Table column name is "DateRelieved" and its data type is DateTime.
while processing the code through .net getting below error.
'The given value of type String from the data source cannot be converted to type datetime of the specified target column.'
{count} votes
SQL seems to accept such strings for datetime fields. Maybe your code needs some adjustments.
@Gani_tpt ,
By the way, it is better to use DATETIMEOFFSET data type instead of the DATETIME data type in SQL Server.
It supports a time zone.
Sorry for the late response.
below is the sample code and XML data.
XML Sample Data
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><RecSearch Val="175"><SearchRes>
<Employee EmpId="MES023912">
<EmpRetired>No</EmpRetired>
<CurrentStatus>true</CurrentStatus>
<DateRelieved>2018-08-20T23:00:00.000Z</DateRelieved>
</Employee>
</SearchRes>
<RecSearch>
.NET Code
SqlBulkCopy SqlBCopy1 = new SqlBulkCopy(con);
for (int i = 1; i <= 10000; i++)
{
catch (WebException ex)
{
}
finally
{
connection.Close();
}
}
The fragment seems to work after some intuitive adjustments. Maybe your program is different, or you should use con instead of connection or viceversa, or use another table instead of Tables[1]. Or maybe your XML file is larger and contains values that cannot be converted.
Getting error if i use DATETIMEOFFSET instead of DATETIME.
Error :
Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type datetimeoffset of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTimeOffset. ---> System.FormatException: String was not recognized as a valid DateTime. at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles, TimeSpan& offset) at System.DateTimeOffset.Parse(String input, IFormatProvider formatProvider, DateTimeStyles styles) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata,
Sign in to comment
@Gani_tpt , based on my test, I find that I can still convert your xml value to DateTime field by your code.
I used the following database design:
Also, I used the folllowing code:
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Thanks for your help.
I found the error when it will come. If date field is blank then we are getting the error.
I am using foreach loop, because i have multiple records in my xml file.
The code is working fine except the date error (if date is blank in xml element ==> Third Record "DateRelieved" is blank.
below sample xml data
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><RecSearch Val="175"><SearchRes>
<Employee EmpId="ME00001">
<EmpRetired>No</EmpRetired>
<CurrentStatus>true</CurrentStatus>
<DateRelieved>2018-08-20T23:00:00.000Z</DateRelieved>
</Employee>
<Employee EmpId="ME0002">
<EmpRetired></EmpRetired>
<CurrentStatus>true</CurrentStatus>
<DateRelieved>2018-08-20T10:00:00.000Z</DateRelieved>
</Employee>
<Employee EmpId="ME0003">
<EmpRetired></EmpRetired>
<CurrentStatus>true</CurrentStatus>
<DateRelieved></DateRelieved> =======> Date is blank.
</Employee>
</SearchRes>
</RecSearch>
SQL Table Design
pls. give us the solution where to correct this error inside handling the loop if date is null...
pls. can anyone help to resolve this datetime conversion error
Thanks it's working...
@Gani_tpt , Sorry for the late response, I suggest that you could set your Fields in database Allow nulls to be true.
I want to confirm if your question is solved now.
It's working and solution is perfect. I accept this answer..
@Gani_tpt , I am glad to hear that your problem has been sloved, now I have converted my comment to answer and you can do it.
Sign in to comment
2 additional answers
Sort by: Most helpful
Have you tried to convert, something like this?
Sign in to comment
Why do you think there is just one table/+1 column.
I see the relation SearchRes => Employee => 3 properties.
SqlBCopy1.DestinationTableName = "tblEmpmployee";
tblEmployee ==> contains 3 columns
SearchRes => Employee => 3 properties.
syntax:
SqlBCopy1.ColumnMappings.Add(SourceColumn, DestinationColumn);
so the code is,
SqlBCopy1.ColumnMappings.Add(col1.ColumnName, col1.ColumnName);
I think that to help you better, we need to see full program together with an XML document that demonstrates the problem. We also need to see the table definition.
Without that, we would have to guess what you are doing, and the error may be in something you don't show us.
Also, when including code in your posts, use that button with ones and zeroes on it. That makes the code more readable.
Sign in to comment
Activity