Error while converting XML date data to SQL column data.

Question

Error while converting XML date data to SQL column data.

Gani_tpt 1,261

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.'

AgaveJoe 20,301 Reputation points

2022-01-19T11:49:08.693+00:00

The error is very clear. Fix the bug by converting the date string "2022-02-25T00:00:00.000Z" to a DateTime type.

Share you code and explain the steps to reproduce this issue if you want community support.
Viorel 89,236 Reputation points

2022-01-19T13:22:26.637+00:00

SQL seems to accept such strings for datetime fields. Maybe your code needs some adjustments.
Yitzhak Khabinsky 20,721 Reputation points

2022-01-19T14:04:18.507+00:00

@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.
Gani_tpt 1,261 Reputation points

2022-01-24T10:06:10.333+00:00
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++)
{

connection.Open(); try { DataSet.ReadXml(dtReader); SqlBCopy1.DestinationTableName = "tblEmpmployee"; foreach (DataColumn col1 in DataSet.Tables[1].Columns) SqlBCopy1.ColumnMappings.Add(col1.ColumnName, col1.ColumnName); SqlBCopy1.WriteToServer(DataSet.Tables[1]); }

catch (WebException ex)
{

}
finally
{
connection.Close();
}

}
Viorel 89,236 Reputation points

2022-01-24T11:56:10.64+00:00

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.
Gani_tpt 1,261 Reputation points

2022-01-28T06:08:54.117+00:00

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,

AgaveJoe 20,301 Reputation points

2022-01-19T11:49:08.693+00:00

The error is very clear. Fix the bug by converting the date string "2022-02-25T00:00:00.000Z" to a DateTime type.

Share you code and explain the steps to reproduce this issue if you want community support.
Viorel 89,236 Reputation points

2022-01-19T13:22:26.637+00:00

SQL seems to accept such strings for datetime fields. Maybe your code needs some adjustments.
Yitzhak Khabinsky 20,721 Reputation points

2022-01-19T14:04:18.507+00:00

@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.
Gani_tpt 1,261 Reputation points

2022-01-24T10:06:10.333+00:00

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++)
{

connection.Open(); try { DataSet.ReadXml(dtReader); SqlBCopy1.DestinationTableName = "tblEmpmployee"; foreach (DataColumn col1 in DataSet.Tables[1].Columns) SqlBCopy1.ColumnMappings.Add(col1.ColumnName, col1.ColumnName); SqlBCopy1.WriteToServer(DataSet.Tables[1]); }

catch (WebException ex)
{

}
finally
{
connection.Close();
}

}
Viorel 89,236 Reputation points

2022-01-24T11:56:10.64+00:00

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.
Gani_tpt 1,261 Reputation points

2022-01-28T06:08:54.117+00:00

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,

Answer 1

Jack J Jun 22,306 Microsoft Vendor

@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:

        DataSet dataSet = new DataSet();  
        dataSet.ReadXml(@"C:\Users\username\Desktop\test1.xml");  
        string connstr = @"connstr";  
        SqlConnection connection = new SqlConnection(connstr);  
        connection.Open();  
        SqlBulkCopy copy = new SqlBulkCopy(connection);  
        copy.DestinationTableName = "Employee";  
        copy.ColumnMappings.Add("EmpRetired", "EmpRetired");  
        copy.ColumnMappings.Add("CurrentStatus", "CurrentStatus");  
        copy.ColumnMappings.Add("DateRelieved", "DateRelieved");  
        copy.ColumnMappings.Add("EmpId", "EmpId");  
        copy.ColumnMappings.Add("SearchRes_Id", "SearchRes_Id");  
        copy.WriteToServer(dataSet.Tables["Employee"]);

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.

Gani_tpt 1,261 Reputation points

2022-01-31T15:06:15.85+00:00

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...
Gani_tpt 1,261 Reputation points

2022-02-01T05:08:49.57+00:00

pls. can anyone help to resolve this datetime conversion error
Gani_tpt 1,261 Reputation points

2022-02-01T14:20:37.627+00:00

Thanks it's working...
Jack J Jun 22,306 Reputation points Microsoft Vendor

2022-02-07T02:17:02.377+00:00

@Gani_tpt , Sorry for the late response, I suggest that you could set your Fields in database Allow nulls to be true.

Thanks it's working...

I want to confirm if your question is solved now.
Gani_tpt 1,261 Reputation points

2022-02-09T11:58:32.777+00:00

It's working and solution is perfect. I accept this answer..
Jack J Jun 22,306 Reputation points Microsoft Vendor

2022-02-10T01:27:01.42+00:00

@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.

Answer 2

Karen Payne MVP 30,441

Have you tried to convert, something like this?

static void Main(string[] args)
{

    XElement element = new("DateRelieved", "2022-02-25T00:00:00.000Z");

    Console.WriteLine(element);
    DateTime parsed = (DateTime)element;
    Console.WriteLine(parsed);
}

Answer 3

Olaf Helper 27,301

SqlBCopy1.ColumnMappings.Add(col1.ColumnName, col1.ColumnName);

Why do you think there is just one table/+1 column.
I see the relation SearchRes => Employee => 3 properties.

Gani_tpt 1,261 Reputation points

2022-01-27T09:53:11.95+00:00

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);
Erland Sommarskog 72,891 Reputation points MVP

2022-01-27T21:53:16+00:00

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.

Error while converting XML date data to SQL column data.

XML Sample Data

.NET Code

2 additional answers

Activity