How to UPDATE XML using Modify

Question

I'm using SQL Server and have Table with XML column. My procedure for insert has input parameter XML document. Is there any way how could I update already existing XML in my table?

This is my OLD XML in my table:

 <weather Location="Paris, France">
   <forecast>
     <description>Sky is clear</description>
     <Date>2013-09-19</Date>
     <MinTemp>13</MinTemp>
     <MaxTemp>20</MaxTemp>
     <Humidity>78</Humidity>
     <Pressure>1024</Pressure>
     <Windspeed>3</WindSpeed>
   </forecast>  
   <forecast>
     <description>Sky is clear</description>
     <Date>2013-09-20</Date>
     <MinTemp>14</MinTemp>
     <MaxTemp>21</MaxTemp>
     <Humidity>75</Humidity>
     <Pressure>1020</Pressure>
     <Windspeed>1</WindSpeed>
   </forecast>  .... 10 times this forecast
 </weather>

This is my new one:

 <weather Location="Paris, France">
   <forecast>
     <description>Sky is clear</description>
     <Date>2013-09-19</Date>
     <MinTemp>14</MinTemp>
     <MaxTemp>21</MaxTemp>
     <Humidity>70</Humidity>
     <Pressure>1000</Pressure>
     <Windspeed>5</WindSpeed>
   </forecast>  
   <forecast>
     <description>Sky is clear</description>
     <Date>2013-09-20</Date>
     <MinTemp>17</MinTemp>
     <MaxTemp>24</MaxTemp>
     <Humidity>68</Humidity>
     <Pressure>1024</Pressure>
     <Windspeed>3</WindSpeed>
   </forecast>  .... 10 times this forecast
 </weather>

What I want now is to update my old XML with values I read from new one. I know I have to somehow go trough my OLD XML, I think I could do that with ...while(exist) and modify, but how to read value I need from my new XML, how to read let's say data for September 19th, and update September 19th in old XML, then read data for September 20th, then update September 20th in old XML etc etc...?

Thanks for help...

Have you looked at [modify()](http://msdn.microsoft.com/en-us/library/ms187093.aspx)? — Mikael Eriksson, Sep 19 '13 at 20:03
Do you want to update just a few single properties - or do you want to basically replace the whole XML in your column with a new XML read from that file? — marc_s, Sep 19 '13 at 20:22
It is hard to understand how you want your XML to be updated. Perhaps you could include in your question what a parameter looks like and what the XML looks like that is already in the table and what you want the XML to look like in the table after the update. Should forecasts be updated or only added? Should they sometimes be deleted? Should location be updated or perhaps used to find where a forecast should be added? — Mikael Eriksson, Sep 19 '13 at 20:27
These two XML files have completely same structure, and I want to read all values from new one and replace old values in XML file in my table. Values I want to replace are Min and Max Temp, Pressure, Humidity and WindSpeed. Based on attribute Location I find XML I need, there can only be one with this Location and same first date, so that's not problem. So, I have old XML, and new one, and I need using modify replace values. — Edna, Sep 19 '13 at 20:53
I still have no idea how to answer this question. I could guess and make assumptions about how you want your XML updated but I rather not do that. You have one answer with a guess and that was obviously not what you where looking for. As I said before, you need to clarify the question if you want an answer. — Mikael Eriksson, Sep 20 '13 at 16:28
I found solution and it works perfect, and all thanks to you Mr Mikael Eriksson! Solutin I needed is here:http://stackoverflow.com/questions/7395942/updating-multiple-xml-nodes-using-t-sql?rq=1, so thanks a lot!!!! This is not totally I need, but I corrected it and now it works! Thanks once again! — Edna, Sep 21 '13 at 00:34

score 1 · Answer 1 · answered Sep 19 '13 at 20:01

1

You may try likethis:-

update tbl1
set myXml.modify('replace value of (/weather/forecast/..)[1] 
                    with concat(string(sql:column("columnname")), "value")')
where myXml.exist('/weather/forecast/..') = 1 //some condition

answered Sep 19 '13 at 20:01

Rahul Tripathi

168,305
31
280
331

I have to read new values from my new XML file, so I'm not quite sure this is what I need.I know I have to use modify and have been trying to do it like that, but I can't write this query correctly. It's like a have to iterate through my new file, and old one, in order to update it, but don't want to do it like that. – Edna Sep 19 '13 at 20:09
@user2710923:- I am really sorry but I didnt get your point. Did my answer helped you or it went in vain??? :( – Rahul Tripathi Sep 19 '13 at 20:11
:) I couldn't even try it because this is not what I was actually looking for. Thanks anyway for your answer. – Edna Sep 19 '13 at 20:14

How to UPDATE XML using Modify

1 Answers1