Notes about data types

The modify() Method

The modify() method allows you to change values directly in your XML stream.

Like all other XML data type methods, it needs an XPath parameter to know which value to change. However, unlike the other methods, modify() works with an UPDATE statement (it will not work with a SELECT statement). Also, modify() can only work with one data value at a time, which is a mathematical and programming concept known as a singleton.

Since there is no limit to the number of elements which can be under another element, any given XPath may have many children. For example, if we have an XML like this:

<week>

  <day>Monday</day>

  <day>Tuesday</day>

  <day>Wednesday</day>

</week>
the XPath of /week/day shown below has three elements and, therefore, is not a singleton:

<day>Monday</day>

<day>Tuesday</day>

<day>Wednesday</day>

However, if you changed your XPath to (/week/day)[1], then you would only get Monday in your result.

<day>Monday</day>

Let's try to get the name of the first label of Radiohead's album "OK Computer".

SELECT xmlData.query('(/albums/album[@title="OK Computer"]/labels/label/text())[1]') 
AS FirstLabelText
FROM dbo.tArtist 
WHERE name = 'Radiohead';

The modify() method has the sole purpose to change a value in an XML document, which is a helpful capability. Suppose an XML document is imported into  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server and you found a typo or need to update just one value. It is no longer necessary to rerun the step to bring in the XML document in order to make that change – you can simply use the modify() method and write the change directly to the XML stream contained in the SQL Server instance.

In this example we'll swap labels of Radiohead’s album "OK Computer". The first label needs to be changed to “Capitol”. And the second one needs to be changed to “Parlophone”.

The modify() method can be used in a SET clause of an UPDATE statement against a variable or a field of xml data type.

UPDATE dbo.tArtist
SET xmlData.MODIFY('replace value of 
(/albums/album[@title="OK Computer"]/labels/label/text())[1] with "Capitol"')
WHERE name = 'Radiohead';

UPDATE dbo.tArtist
SET xmlData.MODIFY('replace value of 
(/albums/album[@title="OK Computer"]/labels/label/text())[2] with "Parlophone"')
WHERE name = 'Radiohead';

Perfect – your revised code runs correctly, and you can see the confirmation:

(1 row(s) affected)

(1 row(s) affected)

Now return to your original query (using the SELECT statement) and run it to confirm that the labels were swapped correctly.

SELECT xmlData.query('/albums/album[@title="OK Computer"]/labels')
FROM dbo.tArtist
WHERE name = 'Radiohead';

<labels>

  <label>Capitol</label>

  <label>Parlophone</label>

</labels>

This task could be done in another way. We could swap elements "label" without replacing their values. We'll just insert a copy of the first label at the end of labels list.

UPDATE dbo.tArtist
SET xmlData.MODIFY('insert (/albums/album[@title="OK Computer"]/labels/label)[1] as last 
into (/albums/album[@title="OK Computer"]/labels)[1]')
WHERE name = 'Radiohead';

UPDATE dbo.tArtist
SET xmlData.MODIFY('delete (/albums/album[@title="OK Computer"]/labels/label)[1]')
WHERE name = 'Radiohead';

To ensure that labels swapped correctly run again the following query:

SELECT xmlData.query('/albums/album[@title="OK Computer"]/labels')
FROM dbo.tArtist
WHERE name = 'Radiohead';

<labels>

  <label>Parlophone</label>

  <label>Capitol</label>

</labels>

Let's try to flag the "Perfect Crime" song from Guns N' Roses' album "Use Your Illusion I" as popular one. For this we’ll add into XML document an attribute isPopular with value 1.

UPDATE dbo.tArtist
SET xmlData.MODIFY('
    insert attribute isPopular { "1" }
    into (/albums[1]/album[@title="Use Your Illusion I"]/song[@title="Perfect Crime"])[1]
')
WHERE name = 'Guns N'' Roses';

 As one more example let's add information about release date and record date of "Estranged" song from Guns N' Roses' album "Use Your Illusion II". Note, that in this example there are two attributes inserting into the element.

UPDATE dbo.tArtist
SET xmlData.MODIFY('
    insert (
        attribute Recorded { "1991" },
        attribute Released { "1994-01-17" }
    )
    into (/albums[1]/album[@title="Use Your Illusion II"]/song[@title="Estranged"])[1]
')
WHERE name = 'Guns N'' Roses';