Linq to XML - update/alter the nodes of an XML Document

Question

I am trying to add a column to a table.To do so I am trying

ALTER TABLE requirements Modify COLUMN parent_id int(11);

but when I try to execute this query mysql does not respond for long.So each time I have to kill the query.

I have created the table using

CREATE TABLE requirements (requirement_id smallint(6) NOT NULL AUTO_INCREMENT,
    product_id smallint(6) NOT NULL, 
    name varchar(255) CHARACTERSET latin1 NOT NULL DEFAULT '', 
    PRIMARY KEY (requirement_id), 
    UNIQUE KEY requirement_product_id_name_idx (product_id,name), 
    UNIQUE KEY requirement_product_idx (requirement_id,product_id), 
    KEY requirement_name_idx_v2 (name) )
    ENGINE=InnoDB
    AUTO_INCREMENT=7365
    DEFAULT CHARSET=utf8;

Please help me know why I am not able to execute the Alter table query.I am new to database is there something wrong with my alter table query.

Solution 1

Is this what you have in mind?

using System;
using System.Linq;
using System.Xml.Linq;

static void Main(string[] args)
{
    string xml = @"<data><record id='1'/><record id='2'/><record id='3'/></data>";
    StringReader sr = new StringReader(xml);
    XDocument d = XDocument.Load(sr);

    // the verbose way, if you will be removing many elements (though in
    // this case, we're only removing one)
    var list = from XElement e in d.Descendants("record")
               where e.Attribute("id").Value == "2" 
               select e;

    // convert the list to an array so that we're not modifying the
    // collection that we're iterating over
    foreach (XElement e in list.ToArray())
    {
       e.Remove();
    }

    // the concise way, which only works if you're removing a single element
    // (and will blow up if the element isn't found)
    d.Descendants("record").Where(x => x.Attribute("id").Value == "3").Single().Remove();

    XmlWriter xw = XmlWriter.Create(Console.Out);
    d.WriteTo(xw);
    xw.Flush();
    Console.ReadLine();
}

Solution 2

The answers are in this thread...you just have to do a lot of sorting to find them, so I've done the work of compliling them for you:

1. YES you can edit elements
2. Deleting elements is easy: element.Remove(); (Remember to save the xDocument after that)

Now if you're reading this thread, you probably want to know HOW to edit elements. There are two different ways that data is stored in xml, e.g.:

<tagName attributeName="some value">another value</tagName>

1. As an ATTRIBUTE on a tag
2. As the content (read value) of the tag

To edit the value of an attribute, knox answered his own question:

d.Descendants("record").Where(x => x.Attribute("id").Value == "2").Single().SetAttributeValue("info", "new sample info");

In other words, get the XElement that you want to alter, and call element.SetAttributeValue("AttributeName", "new value for the attribute")

if you want to edit the value or contents of a tag, then Ajay answered it (if you dig through all his code):

persondata.Element("City").Value = txtCity.Text;

Or, in other words, once you have the XElement you're after, just use .Value and assign away.

Remember that after you perform any of these modifications on the elements in memory, you've got to call .Save() on the XDocument if you want to persist those changes to disk.

Comments

[Abhik Chakraborty]

Did you try ALTER TABLE requirements add COLUMN parent_id int(11); since modify works for existing column not new.

[Jeroen Bleijenberg]

@Robert, what must we include in the code there to ensure that it doesn't blow up? thanks.

[Robert Rossney]

That's what the verbose way above the concise way is for: if you build the list and then iterate over it, the code will work if the list is empty.

[Dan]

Just slapping a block of code in doesn't really help people, especially as a lot of the code isn't related to the actual question

[TonyP]

@Robert,No need to read through Stream, XDocument.Parse(xmlString) does that.