Day: April 5, 2016

reading xml in sql server

jeevanLeave a comment

So some times we want to read xml file in sql server. I am passing list of details to sql server using xml file format. It reduce several db calls when we passing list of data.

Suppose we have List of Students. In List of Student we have to loop it and fire db calls to save data. To overcome this I create xml of List of Student and Pass it to sql server with one db call and then in my stored procedure I will handle those data as follow.

DECLARE @Xml XML
DECLARE @HDoc INT

SET @Xml = '<ArrayOfStudent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Student>
<Id>1</Id>
<FirstName>jeevan</FirstName>
<LastName>JLast name</LastName>
</Student>
<Student>
<Id>2</Id>
<FirstName>john</FirstName>
<LastName>johnLast name</LastName>
</Student>
</ArrayOfStudent>'
EXECUTE SP_XML_PREPAREDOCUMENT @HDoc OUTPUT,@Xml

SELECT [Id],
[FirstName],
[LastName]
INTO #Temp
FROM OPENXML(@HDoc, '/ArrayOfStudent/Student', 1)
WITH (
[Id] INT 'Id',
[FirstName] VARCHAR(400) 'FirstName',
[LastName] VARCHAR(400) 'LastName'
)

EXECUTE SP_XML_REMOVEDOCUMENT @HDoc

SELECT  *  FROM  #Temp

xml reading

Serializing Lists of Classes to XML

jeevanLeave a comment

So some times we want to convert object to xml. We can follow severel cumbersome methods. So I am using Utlity methods to convert c# object to xml and xml to c# object.

Suppose we have class like below


public class Student
 {
 public int Id { get; set; }
 public string FirstName { get; set; }
 public string LastName { get; set; }
 }

And we want serialize List<Student> to xml. So we can use this utlity function


/// <summary>
/// Serialize object to xml
/// </summary>
/// <typeparam name="T">Object type</typeparam>
/// <param name="obj">Object</param>
/// <returns>XML</returns>
public static string ToXml<T>(T obj)
{
using (StringWriter stringWriter = new StringWriter(new StringBuilder(), CultureInfo.InvariantCulture))
{
XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
xmlSerializer.Serialize(stringWriter, obj);
return stringWriter.ToString();
}
}

As a example we populate List<student>  with dummy data


 List <Student> studentList = new List<Student>();

studentList.Add(new Student {
 FirstName = "jeevan",
 LastName = "JLast name",
 Id = 1
 });

studentList.Add(new Student
 {
 FirstName = "john",
 LastName = "johnLast name",
 Id = 2
 });

Then call out utility function


string xml = Utility.ToXml(studentList);

then out xml variable look like as below


<?xml version="1.0" encoding="utf-16"?>
<ArrayOfStudent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Student>
<Id>1</Id>
<FirstName>jeevan</FirstName>
<LastName>JLast name</LastName>
</Student>
<Student>
<Id>2</Id>
<FirstName>john</FirstName>
<LastName>johnLast name</LastName>
</Student>
</ArrayOfStudent>