loading..
Ðóññêèé    English
22:21

XML Data Type Methods page 5

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".

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

FirstLabelText
Parlophone

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.

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

  1. UPDATE dbo.tArtist
  2. SET xmlData.MODIFY('replace value of
  3. (/albums/album[@title="OK Computer"]/labels/label/text())[2] with "Parlophone"')
  4. 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.

  1. SELECT xmlData.query('/albums/album[@title="OK Computer"]/labels')
  2. FROM dbo.tArtist
  3. 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.

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

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

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

  1. SELECT xmlData.query('/albums/album[@title="OK Computer"]/labels')
  2. FROM dbo.tArtist
  3. 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.

  1. UPDATE dbo.tArtist
  2. SET xmlData.MODIFY('
  3.     insert attribute isPopular { "1" }
  4.     into (/albums[1]/album[@title="Use Your Illusion I"]/song[@title="Perfect Crime"])[1]
  5. ')
  6. 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.

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

Bookmark and Share
Pages 1 2 3 4 5 6
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.