How do we combine all data table in one and update in SQL server table using C#

Question

I have the xml file.

I am trying to convert from XML file to dataset. below is the code to convert to dataset.
string res = "";
StreamReader str;
using (str = new StreamReader(request.GetResponse().GetResponseStream()))
{
res = str.ReadToEnd();
StringReader theReader = new StringReader(res);
DataSet.ReadXml(theReader);
}

After converting the dataset, it shows multiple data table based on the XML element.

Sample XML element.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><RecSearch ValSize="175" CPage="1"><SearchRes>
<Employee EmpId="473873627637">
<EmpJoined></EmpJoined>
<EmpRetired>No</EmpRetired>
<EmpAttr1>
<AttriNames>
<AttriName>EMP-NOTEDLIST</AttriName>
</AttriNames>
<AttriType>ATTRI1</AttriType>
</EmpAttr1>
<EmpAttr2>
<AttriNames>
<AttriName>EMP-REQUIREDLIST</AttriName>
</AttriNames>
<AttriType>ATTRI2</AttriType>
</EmpAttr2>
<EmpAttr3>
<AttriNames>
<AttriName>EMP-RESIGNEDLIST</AttriName>
</AttriNames>
<AttriType>ATTRI3</AttriType>
</EmpAttr3>
<Category></Category>
<CurrentStatus>true</CurrentStatus>
<DateRelieved>2018-08-20T23:00:00.000Z</DateRelieved>
</Employee>
</SearchRes>
<RecSearch>

using the above XML output, i am getting different data table like, Employee,EmpAttr1,AttriNames,EmpAttr2,EmpAttr3.

currently, i am uploading the data into SQL server using .NET bulk copy option.

bulk.DestinationTableName = "tbl_Employee";
foreach (DataColumn col in DataSet.Tables[0].Columns)
bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
bulk.WriteToServer(DataSet.Tables[0]);

Here i am uploading only one datable into SQL table.

Instead how do i upload all datatable required fields in to SQL table.

like i want to insert specific fields and data from DataTable[0], DataTable[1], DataTable[2] at one time.

How to do this..?

Final output of my My SQL server table fields should be below.
EmpId ==> 473873627637
EmpJoined ==> Null
EmpRetired ==> Null
EmpAttr1 ==> EMP-NOTEDLIST
EmpAttr2 ==> EMP-REQUIREDLIST
EmpAttr3 ==> EMP-RESIGNEDLIST
Category ==>
CurrentStatus ==> true
DateRelieved ==> 2018-08-20

How do i get the above format in the SQL table from my XML data....?

Answer 1

Hi @Gani_tpt ,

It is much easier to pass XML in its entirety as a parameter to a stored procedure, and shred it there via XQuery.

Check it out below.

SQL

DECLARE @tbl_Employee TABLE (  
	EmpId VARCHAR(20),  
	EmpJoined VARCHAR(20),  
	EmpRetired VARCHAR(20),  
	EmpAttr1 VARCHAR(50),  
	EmpAttr2 VARCHAR(50),  
	EmpAttr3 VARCHAR(50),  
	Category VARCHAR(50),  
	CurrentStatus VARCHAR(50),  
	DateRelieved DATE  
);  
DECLARE @parameter XML =  
N'<RecSearch ValSize="175" CPage="1">  
	<SearchRes>  
		<Employee EmpId="473873627637">  
			<EmpJoined></EmpJoined>  
			<EmpRetired>No</EmpRetired>  
			<EmpAttr1>  
				<AttriNames>  
					<AttriName>EMP-NOTEDLIST</AttriName>  
				</AttriNames>  
				<AttriType>ATTRI1</AttriType>  
			</EmpAttr1>  
			<EmpAttr2>  
				<AttriNames>  
					<AttriName>EMP-REQUIREDLIST</AttriName>  
				</AttriNames>  
				<AttriType>ATTRI2</AttriType>  
			</EmpAttr2>  
			<EmpAttr3>  
				<AttriNames>  
					<AttriName>EMP-RESIGNEDLIST</AttriName>  
				</AttriNames>  
				<AttriType>ATTRI3</AttriType>  
			</EmpAttr3>  
			<Category></Category>  
			<CurrentStatus>true</CurrentStatus>  
			<DateRelieved>2018-08-20T23:00:00.000Z</DateRelieved>  
		</Employee>  
	</SearchRes>  
</RecSearch>';  
  
INSERT INTO @tbl_Employee  
(  
    EmpId,  
    EmpJoined,  
    EmpRetired,  
    EmpAttr1,  
    EmpAttr2,  
    EmpAttr3,  
    Category,  
    CurrentStatus,  
    DateRelieved  
)  
SELECT c.value('@EmpId', 'VARCHAR(20)') AS EmpId  
	, c.value('(EmpJoined/text())[1]', 'VARCHAR(20)') AS EmpJoined  
	, c.value('(EmpRetired/text())[1]', 'VARCHAR(20)') AS EmpRetired  
	, c.value('(EmpAttr1/AttriNames/AttriName/text())[1]', 'VARCHAR(50)') AS EmpAttr1  
	, c.value('(EmpAttr2/AttriNames/AttriName/text())[1]', 'VARCHAR(50)') AS EmpAttr2  
	, c.value('(EmpAttr3/AttriNames/AttriName/text())[1]', 'VARCHAR(50)') AS EmpAttr3  
	, c.value('(Category/text())[1]', 'VARCHAR(50)') AS Category  
	, c.value('(CurrentStatus/text())[1]', 'VARCHAR(50)') AS CurrentStatus  
	, c.value('(DateRelieved/text())[1]', 'DATE') AS DateRelieved  
FROM @parameter.nodes('/RecSearch/SearchRes/Employee') AS t(c);  
  
-- test  
SELECT * FROM @tbl_Employee;  

Answer 2

Here i am uploading only one datable into SQL table. Instead how do i upload all datatable required fields in to SQL table.

That's how SQL works. With one INSERT/UPDATE you can only address one table with the DML statement, so you have to process one table after the other.