XML Datatype Methods

We can use the xml data type methods to modify/query an XML stored in a column of xml datatype.

The methods are as follows
1. Modify()
2. Value()
3. Query()
4. Exist()
5. Nodes()

Today, we will see the use of Modify() method

The Modify() method is used to modify parts of the XML data in the XML column using XQuery Expression

The Syntax is

modify (XML_DML)

The below example shows to add one more job node to the xml

UPDATE Employee SET JobHistory.modify(
'insert < Job JobID="2">
< CompanyName>News Corp< /CompanyName>
< Years>1< /Years>
< /Job>
after (/Job)[1]')
WHERE EMPLOYEEID = 1

The below example shows to replace a value in the xml node.

UPDATE Employee SET JobHistory.modify(
'replace value of (/Job/@JobID)[1] with 3')
WHERE EMPLOYEEID = 1

UPDATE Employee SET JobHistory.modify(
'replace value of (/Job/CompanyName/text())[1] with "BBC Corp"')
WHERE EMPLOYEEID = 1

The below example shows to delete a node in the xml

UPDATE Employee SET JobHistory.modify(
'delete (/Job[@JobID=3])')
WHERE EMPLOYEEID = 1