How to handle blank or null from XML element while SQL Bulk copying

Question

How to handle blank or null from XML element while SQL Bulk copying

BeUnique 2,332

If date field is blank from XML element then we are getting 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>

pls. give us the solution where to correct this error inside handling the loop if date is null...

Anonymous

2022-02-01T09:42:52.437+00:00

Hi GaniTPT, You just need to add a null check to fix this. You can refer to this.

Answer accepted by question author

0 additional answers

Your answer

Anonymous

2022-02-01T09:42:52.437+00:00

Hi GaniTPT, You just need to add a null check to fix this. You can refer to this.

Answer 1

Viorel 125.8K

Try executing this fragment:

var c = dataSet.Tables["Employee"].Columns["DateRelieved"];
foreach( DataRow dr in dataSet.Tables["Employee"].Rows )
{
   if( string.IsNullOrWhiteSpace( dr.Field<string>( c ) ) ) dr[c] = null;
}

Put it before copy.WriteToServer.

BeUnique 2,332 Reputation points

2022-02-01T10:10:11.45+00:00
Thanks for your valuable suggestions.

I have more than10 (ten) date fields available in the XML element.

For that, should i use more than ten DataRow foreach loop...?

modified code below for one datafield.

foreach (DataColumn col1 in dataSet.Tables["Employee"].Columns)
copy.ColumnMappings.Add(col1.ColumnName, col1.ColumnName);

var c = dataSet.Tables["Employee"].Columns["DateRelieved"]; foreach (DataRow dr in dataSet.Tables["Employee"].Rows) { if (string.IsNullOrWhiteSpace(dr.Field<string>(c))) dr[c] = null; }

/*************************
var c2 = dataSet.Tables["Employee"].Columns["Date2"];
foreach (DataRow dr in dataSet.Tables["Employee"].Rows)
{
if (string.IsNullOrWhiteSpace(dr.Field<string>(c2))) dr[c2] = null;
}

var c3 = dataSet.Tables["Employee"].Columns["Date3"];
foreach (DataRow dr in dataSet.Tables["Employee"].Rows)
{
if (string.IsNullOrWhiteSpace(dr.Field<string>(c3))) dr[c3] = null;
}
------------ For Date4
------------ For Date5
------------ For Date6

/**************************************************

copy.WriteToServer(dataSet.Tables["Employee"]);

If i use these many loops, will it affect performance..? or do we have any other alternative idea to handle this..?
BeUnique 2,332 Reputation points

2022-02-01T10:31:07.103+00:00

May be i can use below.

var c = dataSet.Tables["Employee"].Columns["DateRelieved"];
var c2 = dataSet.Tables["Employee"].Columns["Date2"];
var c3 = dataSet.Tables["Employee"].Columns["Date3"];
var c4 = dataSet.Tables["Employee"].Columns["Date4"];
foreach (DataRow dr in dataSet.Tables["Employee"].Rows)
{
if (string.IsNullOrWhiteSpace(dr.Field<string>(c))) dr[c] = null;
if (string.IsNullOrWhiteSpace(dr.Field<string>(c2))) dr[c2] = null;
if (string.IsNullOrWhiteSpace(dr.Field<string>(c3))) dr[c3] = null;
if (string.IsNullOrWhiteSpace(dr.Field<string>(c4))) dr[c4] = null;
}

Am i correct..?

Viorel 125.8K

I think that you can use a single loop:

var c1 = dataSet.Tables["Employee"].Columns["DateRelieved"];
var c2 = dataSet.Tables["Employee"].Columns["Date2"];
var c3 = dataSet.Tables["Employee"].Columns["Date3"];
foreach (DataRow dr in dataSet.Tables["Employee"].Rows)
{
   if (string.IsNullOrWhiteSpace(dr.Field<string>(c1))) dr[c1] = null;
   if (string.IsNullOrWhiteSpace(dr.Field<string>(c2))) dr[c2] = null;
   if (string.IsNullOrWhiteSpace(dr.Field<string>(c3))) dr[c3] = null;
}

-- or:

var table = dataSet.Tables["Employee"];
var cols = new DataColumn [ ] { table.Columns["DateRelieved"], table.Columns["Date2"], table.Columns["Date3"] };
foreach( DataRow dr in table.Rows )
{
   foreach( var c in cols )
   {
      if (string.IsNullOrWhiteSpace(dr.Field<string>(c))) dr[c] = null;
   }
}

BeUnique 2,332 Reputation points

2022-02-01T11:44:33.19+00:00

Awesome result...Thank you very much...

Share via

How to handle blank or null from XML element while SQL Bulk copying

0 additional answers

Your answer