Update SQL server xml column using XQUERY?

Question

I have a simple xml in a XML column

<Bands>
    <Band>
        <Name>beatles</Name>
        <Num>4</Num>
        <Score>5</Score>
    </Band>
    <Band>
        <Name>doors</Name>
        <Num>4</Num>
        <Score>3</Score>
    </Band>
</Bands>

I have managed to update the column with :

   -----just update the name to the id)----
   UPDATE tbl1
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
   with sql:column("id")')

All fine.

Question #1

How can I use this query to udpate the value to id+"lalala":

   UPDATE tbl1
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
   with sql:column("id") + "lalala"')

Error = XQuery [tbl1.myXml.modify()]: The argument of '+' must be of a single numeric primitive type

Question #1

Let's say I Don't want to update first record ([1]) , But I want to udpate (the same update as above) only where score>4.

I can write ofcourse in the xpath :

replace value of (/Bands/Band[Score>4]/Name/text())[1]

But I dont want to do it in the Xpath. Isn't there a Normal way of doing this with a Where clause ?

something like :

   UPDATE tbl1
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
   with sql:column("id")  where [...score>4...]')

here is the online sql

Kattiekatuscha

Answer 1

If you want to concatenate strings you should use concat and if id in your case is an integer you need to cast it to a string in the concat function.

In the where clause you can filter rows of the table to update, you can not specify what nodes to update in the XML. That has to be done in the xquery expression. You can however use exist in the where clause to filter out the rows that really needs the update.

update tbl1
set myXml.modify('replace value of (/Bands/Band[Score > 4]/Name/text())[1] 
                    with concat(string(sql:column("id")), "lalalala")')
where myXml.exist('/Bands/Band[Score > 4]') = 1

Maegan

why did they do this [1] thing ? Didn't they think that maybe i want to update more than 1 element ? – Kattiekatuscha

@RoyiNamir The documentation that says "Expression1 must be a statical singleton." is here. It is the same for insert but delete can delete more than one node. I don't really know why they choose to do it like that but if it was allowed you could end up in some strange situations where the modified nodes changes the predicate that found the nodes that needed modifying in the first place. If you need to update more than one node you have to do it in a while loop. – Maegan

Thanks. I choose this answer for it being less long and more readable. But thanks very much for both answers !. – Kattiekatuscha

why does the [Score > 4] appears both in where and in the xpath above (replace value of (/Bands/Band[Score > 4]/N....)? i mean , now, the where seems redaundent. – Kattiekatuscha

@RoyiNamir The where clause will prevent you from updating rows that does not need the update. Instead of updating every row in the table you only update the rows where Score > 4. – Maegan

That's why i always loved you. :-) – Kattiekatuscha

Answer 2

Q1:

;with t as (select convert(varchar(10),id) + 'lalala' id2, * from #tbl1)
   UPDATE t
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
    with sql:column("id2")');

Note: Do you realise that this updates the name of only the first band's name, not all bands?

2nd Q1:

No you cannot. Especially since (/Bands/Band[Score<4]/Name/text())[1] (I changed to <) specifically targets the doors Band in your example xml in the question. A WHERE clause on the other hand will work across the XML, instead of a particular level in the path. e.g. a very wrong interpretation:

;with t as (
    select a.*, n.m.value('.','int') Score
    from #tbl1 a
    cross apply myXml.nodes('/Bands/Band/Score') n(m) -- assume singular
)
   UPDATE t
   SET [myXml].modify('replace value of (/Bands/Band/Name/text())[1]
    with sql:column("id")')
   where Score < 4

Because there is at least one Band in the xml with a score < 4, the XML gets updated. HOWEVER, because xml.modify only works ONCE on the first match, the first band's name gets updated, not the one matching the score filter.

Selfhood