Is it possible to modify elements within a SQL Server XML Column?

trbenning

Let's say I have a table (MyDataTable) with an id field and an XML column (ItemData) containing the following document:
<Root> <Item name="Item1"> <Description>Item1 Description</Description> </Item> <Item name="Item2"> <Description>Item2 Description</Description> </Item> </Root>

Is it possible to modify Item1's Data element in LinqPad? Here is what I'm trying, but I can't get it to actually update the field. It runs without error, but nothing happens.:
var data = MyDataTable.Where(d => d.Id == 1).FirstOrDefault(); if(data != null) { var description = data.ItemData.XPathSelectElement("/Root/Item[@Name='Item1']/Description"); description.Value = "Different Description"; SubmitChanges(); }

I've also tried setting setting data.ItemData directly with a modified document, but that didn't work either.

trbenning

I guess I should have kept trying a bit longer before posting, because I believe I've solved it. I was able to get it working by setting the XML Column property to an new XElement instance based on the modified version.

var data = MyDataTable.Where(d => d.Id == 1).FirstOrDefault(); if(data != null) { data.ItemData.XPathSelectElement("/Root/Item[@Name='Item1']") .SetElementValue("Description", "Different Description"); data.ItemData = new XElement(data.ItemData); SubmitChanges(); }