save and read xml file into linq database table into column which is xml data type using datacontext and insertonsubmit and submitchaghes function

Question

User1708223868 posted

I have this sql table Which I using in Datacontext in aplication

   CREATE TABLE dbo.Txml(
   brRacuna int NULL,
   korisnickoime varchar(50) NULL,
   xmldata xml NULL
   )

I have xml file which have this look

     <ProizvodUkorpi>
      <proizvod>
       <naziv>fff</naziv>
     <kolicina>2</kolicina>
      <cena>567</cena>
       <iznos>1134</iznos>
       </proizvod>
     <proizvod>
      <naziv>cam</naziv>
      <kolicina>1</kolicina>
        <cena>678</cena>
       <iznos>678</iznos>
        </proizvod>
       <proizvod>
      <naziv>alarm</naziv>
      <kolicina>2</kolicina>
      <cena>3456</cena>
      <iznos>6912</iznos>
      </proizvod>
      </ProizvodUkorpi>

I want to save and read this xml file into linq database table into column xmldata which is xml data type

How I can do that using datacontext and fuction insertonsubmit and submitchanges, I hope someone can help me

Answer 1

User-1614460396 posted

create a stored procedure up_TxmlAddUpdate, use this stored procedure as the insert/update method in your datacontext. make sure the @xmldata a parameter type is nvarchar(max), when you insert to the table  cast your nvarchar to xml.

CAST(@xmldata AS XML)

Answer 2

User-271186128 posted

Hi nmiroslav72,

According to you description, I think you want to read xml file and then save it into LINQ database. Here is a sample, you could refer to it:

My XML File:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<!--Jason's xml-->
<People>
  <Person id="1">
    <Name>Joe</Name>
    <Age>35</Age>
    <Job>Manager</Job>
  </Person>
  <Person id="2">
    <Name>Jason</Name>
    <Age>18</Age>
    <Job>Software Engineer</Job>
  </Person>
  <Person id="3">
    <Name>Lisa</Name>
    <Age>53</Age>
    <Job>Bakery Owner</Job>
  </Person>
  <Person id="4">
    <Name>Mary</Name>
    <Age>90</Age>
    <Job>Nurse</Job>
  </Person>
</People>

Code in page (.aspx)

        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>

Code in page (.aspx.cs)

        protected void Button1_Click(object sender, EventArgs e)
        {
            //read xml file using LINQ
            var names = (from person in XDocument.Load(@"D:\People.xml").Descendants("Person")
                         select new
                         {
                             ID = person.Attribute("id").Value,
                             Name = person.Element("Name").Value,
                             Age = person.Element("Age").Value,
                             Job = person.Element("Job").Value
                         }).ToList();

            MyTestDBDataContext context = new MyTestDBDataContext();
            People pp;
            
            //Insert into Linq Database
            foreach (var item in names)
            {
                pp = new People();
                pp.Id = Convert.ToInt32(item.ID);
                pp.Name = item.Name;
                pp.Job = item.Job;
                pp.Age = Convert.ToInt32(item.Age);

                context.Peoples.InsertOnSubmit(pp);

            }

            context.SubmitChanges();

            var query = from people in context.Peoples
                        select people;
            GridView1.DataSource = query.ToList();
            GridView1.DataBind();
        }

Here are some good articles, you could refer to them:

LINQ To Xml: http://www.dreamincode.net/forums/topic/218979-linq-to-xml/

Basic Queries (LINQ to XML): http://msdn.microsoft.com/en-us/library/bb943906.aspx

Making and Submitting Data Changes (LINQ To SQL): http://msdn.microsoft.com/en-us/library/bb882674(v=vs.110).aspx

Best Regards,
Dillion