CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008xquery
Michas
Michas

Reputation: 9468

TSQL: How to update xml field from value from xml from other table?

I have two (somehow related) tables with XML fields. The problem is I have to update XML of the first with data from XML of the second. Over 100 records. How to do this with TSQL?

I've created something like this.

UPDATE [pwi_new].[dbo].[art]
SET rest.modify('
    replace value of (/root/e[k="alien_id"]/v[1]/text())[1]
    with sql:column("new.rest.value(
        ''(/root/e[k="alien_id"/v)[1]'',''varchar(max)'')
    ")
')
FROM art_new AS new WHERE
    (
        art.title = new.title
        OR art.id = 1352 AND new.id = 119
        OR art.id = 1353 AND new.id = 120
        OR art.id = 1379 AND new.id = 146
        OR art.id = 1380 AND new.id = 147
    )
    AND art.parent = 1460

However, it doesn't work. I've got this error

XQuery [pwi_new.dbo.art.rest.modify()]: ',' or ')' expected

on this line:

''(/root/e[k="alien_id"/v)[1]'',''varchar(max)'')

and have no idea how to fix, if possible.

It looks it is not possible without subqueries, cte, views and so on.

TSQL: How can I update the value of an xml tag with the value of an xml tag from another related table?
I know this question. I have over 100 records, so I would like an option that doesn't require sql:variable

Upvotes: 1

Views: 3456

Answers (1)

Adam Wenger
Adam Wenger

Reputation: 17570

Reading the link in your question, I used that idea, and modified your starting query to include a CTE to remove the requirement on sql:variable.

I do not have any xml data to test this with right now, but something like this should work (syntax will be close, not too familiar with xquery)

WITH NewXmlData AS
(
   SELECT art.Id AS artId, new.Id AS newId
      , new.rest.value( '(/root/e[k="alien_id"/v)[1]','VARCHAR(MAX)' AS newValue
   FROM [pwi_new].[dbo].[art]
      , art_new AS new
   WHERE
   (
      art.title = new.title
         OR art.id = 1352 AND new.id = 119
         OR art.id = 1353 AND new.id = 120
         OR art.id = 1379 AND new.id = 146
         OR art.id = 1380 AND new.id = 147
   )
      AND art.parent = 1460
)
UPDATE art
SET rest.modify('
    replace value of (/root/e[k="alien_id"]/v[1]/text())[1]
    with sql:column("NewXmlData.newValue")
    ")
')
FROM [pwi_new].[dbo].[art]
INNER JOIN NewXmlData ON art.Id = NewXmlData.Id

Upvotes: 3

Related Questions