Inserting and Selecting XML data in SQL Server with ASP.NET (C#)

Question

I have a field in a SQL Server database table of datatype XML.

The proposed format is as follows:

<remarks>
    <remark>
        <author>Patrick Keane</author>
        <date>18/12/2012 10:06</date>
        <content>My content Here</content>
    </remark>
    <remark>
        <author>Joe Blogs</author>
        <date>19/12/2012 11:32</date>
        <content>My content Here</content>
    </remark>
    ......
    ......
</remarks>

Using ASP.NET (C#), I want to add a new entry to the field (which may or may not already have entries in it). I also want to be able to retrieve the last entry (for display on my .aspx page) and the full list of entries (for display on a .xml page).

I've been looking about for information/tutorials and most of what I find is exporting data and converting to XML. Can anybody point me to a relevant tutorial or provide some info for me? Ty

score 0 · Answer 1 · answered Dec 18 '12 at 12:29

0

To Insert :

Pass the xml you want to append in the stored procedure as a parameter and then try following query :

update [dbo].[TableName]
  set remark.modify ('insert sql:variable("@varibleName") as last into (/remarks/)[1]') WHERE [Condition]

answered Dec 18 '12 at 12:29

Dev

6,570
10
66
112

score 0 · Answer 2 · edited May 23 '17 at 12:04

You can use a class that hold the data you won to export (and import) to xml.
You use the [Serializable] for make the class available for xml export. So for example if you have this class:

[Serializable]
public class MyClassThatKeepTheData
{
    public List<int> cListWithValues;

    public int Value1;

    public int Value2;
}

Then you can use the XmlSerializer to convert it to XML (and vice verse) as:

public static string ObjectToXML(Type type, object obby)
{
    XmlSerializer ser = new XmlSerializer(type);
    using (System.IO.MemoryStream stm = new System.IO.MemoryStream())
    {
        //serialize to a memory stream
        ser.Serialize(stm, obby);
        //reset to beginning so we can read it.  
        stm.Position = 0;
        //Convert a string. 
        using (System.IO.StreamReader stmReader = new System.IO.StreamReader(stm))
        {
            string xmlData = stmReader.ReadToEnd();
            return xmlData;
        }
    }
}

public static object XmlToObject(Type type, string xml)
{
    object oOut = null;    

    if (xml != null && xml.Length > 0)
    {
        System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(type);

        using (System.IO.StringReader sReader = new System.IO.StringReader(xml))
        {
            oOut = serializer.Deserialize(sReader);

            sReader.Close();
        }
    }

    return oOut;
}

and make the convert to XML as:

MyClassThatKeepTheData cTheObject = new MyClassThatKeepTheData();

ObjectToXML(typeof(MyClassThatKeepTheData), cTheObject)

Relative: How to optimize class for viewstate

and I suggest also the protobuf-net is not XML, but its a lot faster and do the same work

Inserting and Selecting XML data in SQL Server with ASP.NET (C#)

2 Answers2