DML operations on multiple nodes

  • [アーティクル]

If you've been playing with SQL Server 2005's implementation of XQuery for a while you probably know that "delete" is the only operation that allows for multiple target nodes.

For example, let's imagine that we have the following table

CREATE TABLE mySchemas (xmlCol XML)
go

Let's say we use this table to store XML schemas (this is possible since schemas are themselves XML documents). We might want to remove all xsd:unique nodes before importing those schemas in a schema collection.
We can run the following query

UPDATE mySchemas SET xmlCol.modify('delete //xs:unique')
go

The static type of expression //xs:unique is not a singleton. If there are multiple xs:unique nodes in a given instance they will all be deleted.

Things are different for the "insert" and "replace value of" operations. As you might remember from an earlier post, a static type check will be performed to ensure that the path expression yields one node at the most.

Let's go back to typed XML and define the following schema

create xml schema collection Items as '
<schema xmlns="https://www.w3.org/2001/XMLSchema" targetNamespace="retail:items" xmlns:ns="retail:items">

 <element name="item">
<complexType>
<sequence>
<element name="description" type="string"/>
<element name="price" type="decimal"/>
</sequence>
</complexType>
</element>

</schema>'
go

Now let's imagine that we have an XML instance and want to switch all prices to 99 cents. We can try this:

DECLARE @var XML(Items)
SET @var = '<ns:item xmlns:ns="retail:items">
<description>Toothpicks x 250</description>
<price>1.99</price>
</ns:item>
<ns:item xmlns:ns="retail:items">
<description>Tomato soup 10.75 OZ</description>
<price>1.49</price>
</ns:item>'
SET @var.modify('replace value of //price with 0.99')
go

Predictably this fails because of static typing. The error message is
XQuery [modify()]: The target of 'replace' must be at most one node, found 'element(price,xs:decimal) *'

We cannot modify the values of multiple nodes all at once. But we can very easily count the number of nodes to update and replace the values once by once like this

DECLARE @var XML(Items)
SET @var = '<ns:item xmlns:ns="retail:items">
<description>Toothpicks x 250</description>
<price>1.99</price>
</ns:item>
<ns:item xmlns:ns="retail:items">
<description>Tomato soup 10.75 OZ</description>
<price>1.49</price>
</ns:item>'

DECLARE @iCOunt int
SET @iCount = @var.value('count(//price)','int')

DECLARE @i int
SET @i = 1

WHILE (@i <= @iCount)
BEGIN
SET @var.modify('replace value of ((//price)[sql:variable("@i")])[1] with 0.99')
SET @i = @i + 1
END

SELECT @var

The resulting instance will be

<ns:item xmlns:ns="retail:items">
<description>Toothpicks x 250</description>
<price>0.99</price>
</ns:item>
<ns:item xmlns:ns="retail:items">
<description>Tomato soup 10.75 OZ</description>
<price>0.99</price>
</ns:item>

Here is a brief explanation of what this script does. First, we count how many price elements there are in the instance and we store that value in @iCount.
Then we iterate over those nodes, using a count variable @i. The use of sql:variable("@i") in a position predicate ensure that every time @i is incremented we target the next node in the sequence.
You'll also notice that I still had to apply a [1] position predicate. That is because the static analysis of expression (//price)[sql:variable("@i")] will return element(price, xs:decimal)*. As you might remember from one of my earlier posts, only the presence of an integer literal or of a call to the last() function in a predicate can be seen as a guarantee that no more than one node will be returned. The server won't analyze more complex expressions like the one we used.
Finally, you also want to remember that with XQuery sequence indexing starts at 1, which is why before entering the WHILE loop I set the @i variable to 1 and used "less than or equal to" in the WHILE condition.
A C or C++ programmer might do something like

SET @i = 0
WHILE (@i < @iCount)
...

which would not fail outright but would yield the wrong results (all price elements would be updated except the last one).

-
Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm.

Comments

  • Anonymous
    December 07, 2005
    The comment has been removed
  • Anonymous
    December 27, 2005
    That's correct Steve, we do not support xsd:unique. We also reject schemas that use xsd:key and xsd:keyref. I haven't seen any plans to support them in the near future. If this is a concern for you I suggest you contact my PM Shankar Pal at http://blogs.msdn.com/spal and discuss this with him. Feel free to keep me in the loop also. And if anybody else fels these are needed features please make yourselves known!

    Sorry for the long delay in responding. I'm on vacation (the foreign keyboard also explains any typo that might be in this mesage).