Read, write and update xml data type.

Hi All,

I would like to learn about xml data type of sql server 2005. I am using c# to develop a project that will use sql server express as a database. What I want to accomplish in my project is to serialize an object and save into a field with xml data type. Also I want to have same functionality in other way around. Retrieve this xml representation of the object, Deserialize it so get the saved object back into application.

I would be happy If you can provide me some code which shows how to accomplish this task or some links that directs me to the appropriate docs.

Thanks in advance.

There is lots of material on MSDN, there is a section about the xml data type with subsections about the methods of the xml data type (i.e. query, value, exist, modify, nodes) and the XML DML (XML data modification language).|||

Hi Martin,

Thanks for the reply and links. I haven't gone through the links you sent completly but I think the subject I typed here is misleading so let me explain a bit more, the difficulty I have. . I have no problem with creating a table with an xml type field. I believe by reading the links you have sent I can perform insert, update and delete functions. My difficulty starts just after the seriliazation of an object or just before the deserialization of the xml data I read from xml field. Both serialize and deserialize methods trys to write/read to/from a file or a stream. All I want is to hold this xml in a data structure where I can use it at the time of serialization or deserialization.

The original situaltion:

I have a form to be filled out by the users of my application. Since there are so many fields of data in this form I dont want to create a table with somany fields. So I am planing to hold info entered by user in an xml field. (and ofcourse I should be able to read the data back from this xml field and display in the app)

Thanks.

|||

Got it worked

I used string/TextWriter and string/TextReader combinations and worked fine. Thanks.

FlatWhite

|||

Hi FlatWhite

I would like to do the same thing you are doing. Could you provide some code snippets for me on how to do it?

Thanks

|||

Well I am neither SQL nor C# expert so you can use my code at your own risk J

First of all I am assuming that you have an SQL database table which has ID and XML fields and also you have InsertObj and SelectObj stored procedures.

TABLE : ObjectTable

ObjID-int-identity

ObjXML-XML

SPs : InsertObj

SelectObj

Code Snippet

CREATE PROCEDURE InsertObj

@.ObjXML xml

AS

BEGIN

INSERT INTO ObjectTable (ObjXML)

VALUES (@.ObjXML)

END

CREATE PROCEDURE SelectObj

@.ObjID int

AS

BEGIN

SELECT ObjXML

FROM ObjTable

WHERE ObjID = @.ObjID

END

I am also assuming that you have class Ojb with three properties property1, property2 and property3 and a form with 4 textboxes

Here is how you can serialize an object and save it in an XML field. (There might be some better way of doing this but sofar no one made a comment on this)

Code Snippet

Obj c = new Obj();

c.property1 = textBox1.Text;

c.property2 = textBox2.Text;

c.property3 = textBox3.Text;

XmlSerializer s = new XmlSerializer(typeof(Obj));

System.Text.StringBuilder builder = new System.Text.StringBuilder();

s.Serialize(XmlWriter.Create(builder),c);

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=Server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "InsertObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjXML", System.Data.SqlDbType.Xml);

command.Parameters[0].Value = builder.ToString();

command.ExecuteNonQuery();

conn.Close();

And this is how you can deserialize an XML field and get back the saved object.

Code Snippet

XmlReaderSettings set = new XmlReaderSettings();

set.ConformanceLevel = ConformanceLevel.Fragment;

Obj c = new Obj();

SqlConnection conn = new SqlConnection();

conn.ConnectionString = @."Data Source=server;Initial Catalog=database;Integrated Security=SSPI;";

conn.Open();

SqlCommand command = conn.CreateCommand();

command.CommandText = "SelectObj";

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Parameters.Add("@.ObjID", System.Data.SqlDbType.Int);

//taking the input from textBox4.

command.Parameters[0].Value = Convert.ToInt32(textBox4.Text);

SqlDataReader datareader = command.ExecuteReader();

System.Text.StringBuilder builder = new System.Text.StringBuilder();

XmlSerializer s = new XmlSerializer(typeof(Obj));

while(datareader.Read())

{

builder.Append(datareader[0]);

}

TextReader tr = new StringReader(builder.ToString());

c = (CObj)s.Deserialize(tr);

tr.Close();

textBox1.Text = c.property1;

textBox2.Text = c.property2;

textBox3.Text = c.property3;

I hope it helps.