Updating xml in sql server 2016
The value() Method: The value() method is used to retrieve a value of SQL type from an XML instance.
The value() method works similar as a combination of query() method with text() method, except that value() method allows us to define the data type.
If you don’t require specifying the data type then query() method is best in that scenario, but if you want to define some specific data type like float, numeric or money, then you should prefer the value() method instead of query() method.
Example: Output: In the above example exist method return false(0) because employee only contains a single name attribute for each instance, so it doesn’t find second name attribute.
So modern database engines came along to store the XML data along with the relational data type.
2005 started to store the XML data using xml data type.
The modify() method take the XPath as parameter to which part of xml will update, and modify() method only takes a single data value at a time.
Example: In the above example we updated the value of salary for Employee for which value of id is 1(Employee_Details. Let us check the value Employee_Details table again.
XML data type Methods: SQL Server provide the following five XML data type methods for extracting or manipulating the xml data.If we use '/employee/name' for XPath then it specify that we want to navigate to first name of employee.The ‘student/name/subject’ for XPath means we want to navigate the second subject of first name of student node.We can see that salary for “Pankaj Choudhary” employee has been changed to 45000.Example: Output: In above example we delete the second employee record from each row.
Each xml record contain a unique id and 3 fragments (name, city and salary).