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

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