Modify XML data in SQL

 

 

 

Today, the data on the cloud went wrong. As a result, the original script bat was renamed cmd. Only data can be modified.

Original data:

<VStormCliCreator>  <MachineName>B1-Site</MachineName>  <NIC>TrunkNetworkSwitch</NIC>  <Host>MSD-1531346.fareast.corp.microsoft.com</Host>  <Memory>4096</Memory>  <Password>User@123</Password>  <VLanID>979</VLanID>  <PostScripts>    <PostScript>\\172.23.93.230\scratch$\v-XXX\Run.bat</PostScript>  </PostScripts>  <ImageName>97-SP2</ImageName>  <OSID>97</OSID></VStormCliCreator>

 

Modify command:

update dbo.MachineConfigs set CreatorConfig.modify('replace value of 

(/VStormCliCreator/PostScripts/PostScript/text())[1] with ("\\172.23.93.230\scratch$\v-XXX\Run.cmd")') 

where ID = '49E6303D-577D-43C8-BB57-0533FC19BBEC' select * from dbo.MachineConfigs  where ID = '49E6303D-577D-43C8-BB57-0533FC19BBEC'

 

The following data:

<VStormCliCreator>  <MachineName>B1-Site</MachineName>  <NIC>TrunkNetworkSwitch</NIC>  <Host>MSD-1531346.fareast.corp.microsoft.com</Host>  <Memory>4096</Memory>  <Password>User@123</Password>  <VLanID>979</VLanID>  <PostScripts>    <PostScript>\\172.23.93.230\scratch$\v-XXX\Run.cmd</PostScript>  </PostScripts>  <ImageName>97-SP2</ImageName>  <OSID>97</OSID></VStormCliCreator>

I would like to thank Guo Yongcheng for his http://blog.csdn.net/tjvictor/archive/2009/07/21/4368496.aspx on how to insert update Delete XML data in SQL Server.

Modify Method

This method can update XML data. The insert, delete, and update keywords added in XQuery provide support for xml dml. You can use the insert, delete, and update keywords to insert, delete, and update one or more nodes respectively. For example, enter the following code in the query window:

 UPDATE books SET xmlCol.modify(  'insert  <section num="1">  <content>Background</content> </section> after (/book/title)[1]')  where id = 1

Use the update statement to modify the xmlcol field in the books table in row 1st of the Code section ~ Use Insert in 7 rows

Code
   1 <book type="computer" publicationdate="2008" ISBN="0-7356-1588-2">   
 2  <title>c#</title>    
 3  <author>      
 4 <first-name>sheng</first-name>
 5 <last-name>bin</last-name>    
 6 </author>    
 7 <author>      
 8 <first-name>gengxin</first-name>      
 9 <last-name>sun</last-name>    
10 </author>    
11 <price>35.99</price>  
12 </book> 
 
After executing this code segment, add the element section to the xmlcol column of the record with the ID column value of 1. This element will be added to the title element. The content of the modified xmlcol column is as follows.
Code
    1 <book type="computer" publicationdate="2008" ISBN="0-7356-1588-2">    
 2 <title>c#</title>    
 3 <section num="1">      
 4 <content>Background</content>    
 5 </section>    
 6 <author>      
 7 <first-name>sheng</first-name>      
 8 <last-name>bin</last-name>    
 9 </author>    
10 <author>     
11 <first-name>gengxin</first-name>      
12 <last-name>sun</last-name>    
13 </author>    
14 <price>35.99</price>  
15 </book>