Getting error while XML parsing data to stored procedure in C#

Question

I am getting error while i am passing xml data to stored procedures.

i am not sure where is the problem in my insert stored procedure.

can anyone suggest to pass the error...?

Error : System.Data.SqlClient.SqlException: 'XML parsing: line 1, character 55, unable to switch the encoding'

CREATE TABLE [dbo].[tblEmpDetails](
	[EmpId] [bigint] NOT NULL,
	[Name] [varchar](100) NULL,
	[Dob] [datetime] NULL,
	[Address1] [varchar](500) NULL,
	[Address2] [varchar](500) NULL,
 CONSTRAINT [PK_tblEmpDetails] PRIMARY KEY CLUSTERED 
(
	[EmpId] ASC
)
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[InsertXML_Data]
@xml XML
AS
BEGIN
      INSERT INTO tblEmpDetails (Dob,Name,Address1,Address2,EmpId)
      SELECT
	  cast(Document.value('@Dob','datetime') as datetime) AS Dob, 
	  Employee.value('(Name/text())[1]','VARCHAR(MAX)') AS Name, 
	  Employee.value('(Address1/text())[1]','VARCHAR(100)') AS Address1, 
	  Employee.value('(Address2/text())[1]','VARCHAR(100)') AS Address1, 
          Employee.value('@EmpId','BIGINT') AS EmpId 
      FROM
      @xml.nodes('//Employee')AS TEMPTABLE(Employee)
END

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<RecSearch TRecords="1521" TotalSize="25">
<SearchRes>
	<Employee EmpId ="1">
    		<Name>John Hammond</Name>
		<Dob>1987-07-21T23:00:00.000Z</Dob>
		<Address1>
			<AddressTypeNames/>
			<AddressTypeType>Address1_USA</AddressTypeType>
		</Address1>
		<Address2>
			<AddressTypeNames/>
			<AddressTypeType>Address2_AUS</AddressTypeType>
		</Address1>
  	</Employee>
  	<Employee EmpId = "2">
    		<Name>Mudassar Khan</Name>
    		<Country>India</Country>
		<Dob></Dob>
		<Address1>
			<AddressTypeNames/>
			<AddressTypeType>Address1_IND</AddressTypeType>
		</Address1>
		<Address2>
			<AddressTypeNames/>
			<AddressTypeType>Address2_SING</AddressTypeType>
		</Address2>
  	</Employee>
</SearchRes>
</RecSearch>

pls. tell us where is the problem of every tag i am calling in the stored procedure...

especially in datatime(dob) and address1, address2.

cast(Document.value('@Dob','datetime') as datetime) AS Dob, 	  Employee.value('(Name/text())[1]','VARCHAR(MAX)') AS Name, 	  Employee.value('(Address1/text())[1]','VARCHAR(100)') AS Address1, 	  Employee.value('(Address2/text())[1]','VARCHAR(100)') AS Address1,

complete C# code as below

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                        using (SqlConnection con1 = new SqlConnection(constr))
                        {
                            using (SqlCommand cmd1 = new SqlCommand("InsertXML_Data"))
                            {
                                cmd1.Connection = con1;
                                cmd1.CommandType = CommandType.StoredProcedure;
                                cmd1.Parameters.AddWithValue("@xml", response);
                                con1.Open();
                                cmd1.ExecuteNonQuery();
                                con1.Close();
                            }
                        }

Answer 1

To work around the problem, try to pass the XML as a string, and adjust the procedure:

ALTER PROCEDURE [dbo].[InsertXML_Data]
@xml_string varchar(max)
AS
BEGIN
   declare @xml xml = cast(@xml_string as xml)
   INSERT INTO ...
   ...

Answer 2

: 'XML parsing: line 1, character 55, unable to switch the encoding' <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

By some reasons I don't know XQuery don't allow the "encoding" paramter; remove it

encoding="UTF-8"