Help required on XML databases-If you want to manupulate XML data in front end(.Net) you can use serialization.

We are currently using SQL server database in our project. In future, we are planning to store all data using XML i.e. instead of having various columns in table, we will have only one column of XML type which will contain XML with data.

 

We are currently using Entity Framework (EF) for connecting to the database. As per articles available on net, we can’t use EF with XML database. I have tried using EF but that doesn’t work due to some reasons. So the only option that remains with us is XQuery.

 

Has anyone of you used XML databases in your project with XQuery? Is there any framework which can be used to query XML data using XQuery?

 

If yes, can you please guide us?

 

Example: Employee data in various departments will be stored in table’s XML type column as

 

Data

<Department> <DepartmentId>1</DepartmentId> <Employees> <Employee>     <Name>Demo1</Name>     <Salary>10000</Salary>     <Designation>Developer</Designation>     <RegionId>1</RegionId>   </Employee>   <Employee>     <Name>Demo2</Name>     <Salary>12000</Salary>     <Designation>Sr. Developer</Designation>     <RegionId>2</RegionId>   </Employee>  </Employees> </Department>

<Department> <DepartmentId>2</DepartmentId> <Employees> <Employee>     <Name>Demo3</Name>     <Salary>15000</Salary>     <Designation>Developer</Designation>     <RegionId>1</RegionId>   </Employee>   <Employee>     <Name>Demo4</Name>     <Salary>16000</Salary>     <Designation>Sr. Developer</Designation>     <RegionId>2</RegionId>   </Employee>  </Employees> </Department>

 

SOLUTION 1:


If you want to manupulate XML data in front end(.Net) you can use serialization.

 

(A) While saving  collect data from front end convert it to XML(Serialize) and save.

 

    Exaple:

        private string GenerateXML(ReqCollection reqCollection)

        {

            if (reqCollection != null && reqCollection.RequirementList != null && reqCollection.RequirementList.Count > 0)

            {

                XmlSerializer serializer = new XmlSerializer(typeof(ReqCollection));

                StringWriter writer = new StringWriter();

                serializer.Serialize(writer, reqCollection);

                return writer.ToString();

            }

           return string.Empty;

        }

(B) Retrieve - Send XML from SQL and Deserialize and convert to a collection/object

    Example : 

        private ReqCollection DeserializeXML(string xmlString)

        {

                if (!string.IsNullOrEmpty(xmlString))

                {

                    StringReader reader = new StringReader(xmlString);

                    XmlSerializer serializer = new XmlSerializer(typeof(ReqCollection));

                    return (ReqCollection)serializer.Deserialize(reader);

                }

                return new ReqCollection();

        }

You can control the root names as follows

 [XmlRoot("Employees")]

    public class ReqCollection

    {

        [XmlArray("Employee"), XmlArrayItem(typeof(Requirement), ElementName = "Employee")]

        public List<Requirement> RequirementList { get; set; }

    }

    [Serializable]

    public class Employee

    {

        public int Id { get; set; }

        public string Name{ get; set; }

        public string Salary{ get; set; }

    }

Hope this helps...