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.
