sql server 2005 - XPath in T-SQL update schema -


I have a column with XML data in my table

for example We say that the schema is below: 

  & lt; Person & gt; & Lt; Id & gt; & Lt; / Id & gt; & Lt; Name & gt; & Lt; / Name & gt; & Lt; / Person & gt;   
 The problem is that some of these nodes have an extra node  & lt; Lastname & gt;  has been found  
 Now I want to update all the elements on this column so that each one got the node  last name  with the default value, for example < Code> smith .   
 
 
 Do something like this:  
  UPDATE dbo.YourTable SET XmlColumn.modify ('Insert and lieutenant; last name & gt; Smith & lt; / lastname & gt; person' [1] ') where XmlColumn.exist (' / person / lastname ') = 0   
 This update updates all the rows where  & lt; Lastname & gt;  node              P>  Update:  If you want to choose some name, use this query:  
  UPDATE dbo.YourTable SET XmlColumn.modify ('Insert & lt; Last name & gt; Smith & lt; / Lastname & gt; in final form (/ person) [1] ') where XmlCol to prove my query -' '' (person / person [name = "John" ] ') ​​= 1   
  Update # 2:  - Try it a little bit here:  
  announcement @ test table (ID INT, XmlCol XML) @ test value (IN, & lt; aa D & gt; 2 & lt; name & gt; Fred & lt; / name & gt; & lt; / person & gt; John & lt; / name & gt; & lt; / Person & gt; '), (2,' & lt; person's name = "john" '), (3,' person & gt; & lt; id & gt; 3 & lt; / id & gt; ; & Lt; name & gt; Wallis & lt; / name & gt; & lt; / person & gt; 'SELECT * FROM @test WHERE XmlCol.exist (' / person [if you want it (SQL Server 2008 or newer On), you will get:  
  1  ; Person & gt; & Lt; ID & gt; 1 & lt; / Id & gt; & Lt; Name & gt; John & lt; / Name & gt; & Lt; / Person & gt;   
 as output; Selection criteria  XML element            < / P>  >

Comments

Post a Comment