Updating xml in sql

How do I update the xml field to remove that portion.also say in the future we add an element how do I add the element?thanks As per BOL as well as Jacob Sebastians great Book "The Art of XSD" there is no way to remove an element from a stored schema using T-SQL.The only way to alter the specific schema is to drop it an re-create it without the element in question.You will not, however, enjoy the full capabilities of the Info Path editing environment, which offers declarative design features, standard Microsoft Office features such as rich-text formatting, the ability to check spelling, or the offline capability of editing a form without a network connection. there is existing data out there that needs to be updated.But it only updates the first occurrence of each row and in my case there can be many occurrences on a row. or do I have to run it several times and change the [1] to [2] and so on?

updating xml in sql-47updating xml in sql-34

/Kaunda UPDATE t SET ADDRESS.modify('replace value of (/ROOT[1]/ADDRESS[1]/STREET/text())[1] with sql:column("Strt")') FROM ( SELECT ADDRESS, REPLACE(ADDRESS.query('/ROOT/ADDRESS/STREET').value('.','varchar(100)'),'Maix','Main') AS Strt FROM LOGG) t Thanks a lot, it works pretty good right now!The LOGG table has a numer of columns and one of them is the XML column lets call it ADDRESS and it's just some of the node values in that particular column that I want to modify (the misspelled ones).The misspelled node values can be found anywhere in the XML column and there might be more than one misspelled values in the same row and column! Isn't your solution just a way to create and update a temporary table?The Web service uses the method to accept changes to the records.Both methods are used in the Info Path form template and the ASP.