I am generating an xml from my C# application which I am then sending to a SQL Server stored procedure. When the stored procedure parses the xml, then it is throwing error
XML parsing: line 1, character 39, unable to switch the encoding
This is the way I am generating my XML. The function is an extension function.
public static string ToXml<T>(this T obj, string rootName)
{
XmlSerializer serializer = new XmlSerializer(typeof(T), new XmlRootAttribute(rootName));
var xmlNs = new XmlSerializerNamespaces();
xmlNs.Add(string.Empty, string.Empty);
using (StringWriter sw = new StringWriter())
{
serializer.Serialize(sw, obj, xmlNs);
return sw.ToString();
}
}
and the stored procedure looks like this:
/*
SET @Data = '<?xml version="1.0" encoding="utf-16"?>
<Students>
<Student>
<ID>2</ID>
<FirstName>Soument</FirstName>
<LastName>Sen</LastName>
<IsActive>true</IsActive>
<StateID>2</StateID>
<StateName>Bihar</StateName>
<CityID>4</CityID>
<CityName>DharBganga</CityName>
</Student>
</Students>'
*/
CREATE PROCEDURE [dbo].USP_SaveStudent
(
@Data xml,
@PageNbr INT = 1,
@PageSize INT = 10,
@SortColumn NVARCHAR(20) = 'ID',
@SortOrder NVARCHAR(4)= 'ASC'
)
AS
BEGIN TRY
MERGE INTO Student as Trg
USING (SELECT
d.x.value('id[1]', 'varchar(MAX)') as ID,
d.x.value('FirstName[1]', 'varchar(MAX)') as FirstName ,
d.x.value('LastName[1]', 'varchar(MAX)') as LastName,
d.x.value('StateID[1]','int') as StateID,
d.x.value('CityID[1]','int') as CityID,
d.x.value('IsActive[1]','bit') as IsActive
FROM
@Data.nodes('/Students/Student') as d(x)) AS Src ON Trg.ID = Src.ID
WHEN MATCHED THEN
UPDATE
SET Trg.FirstName = Src.FirstName,
Trg.LastName = Src.LastName,
Trg.StateID = Src.StateID,
Trg.CityID = Src.CityID,
Trg.IsActive = Src.IsActive
WHEN NOT MATCHED BY TARGET THEN
INSERT (FirstName, LastName, StateID, CityID, IsActive)
VALUES(Src.FirstName, Src.LastName, Src.StateID, Src.CityID, Src.IsActive);
END TRY
BEGIN CATCH
-- Insert Error into table
--INSERT INTO #error_log(message)
--VALUES (ERROR_MESSAGE());
END CATCH
EXEC USP_GetStudentData @PageNbr, @PageSize, @SortColumn, @SortOrder
Looking for guidance like how to fix it. Thanks
