CODEWITHSUNDEEP
sql-servert-sqlxml-dml
Dimon_Tools
Dimon_Tools

Reputation: 21

Is there a way to update xml column using subquery column value?

I'm trying to update one XML column by the values from another XML column of the same table like:

source:
|----|--------------------|----------------------|
|F_ID|F_DATA              |F_RESULT              |
|----|--------------------|----------------------|
|1   |<Email guid="qwe" />|<Request guid="" />   |
|----|--------------------|----------------------|
result:
|----|--------------------|----------------------|
|F_ID|F_DATA              |F_RESULT              |
|----|--------------------|----------------------|
|1   |<Email guid="qwe" />|<Request guid="qwe" />|
|----|--------------------|----------------------|

Thus, I've tried this:

DROP TABLE IF EXISTS #F_DATA;
CREATE TABLE #F_DATA(
    F_ID BIGINT IDENTITY PRIMARY KEY,
    F_DATA XML,
    F_RESULT XML
);
INSERT INTO #F_DATA(F_DATA, F_RESULT)
VALUES('<Email guid="qwe" />', '<Request guid="" />');

UPDATE FU
SET FU.F_RESULT.modify('replace value of (Request/@guid)[1] with {sql:column("F_VALUE")}')
FROM
    #F_DATA FU
INNER JOIN 
    (SELECT
         F_ID,
         F_DATA.value('(Email/@guid)[1]', 'NVARCHAR(MAX)') 'F_VALUE'
     FROM
         #F_DATA) V ON FU.F_ID = V.F_ID;

DROP TABLE IF EXISTS #F_DATA;

An error

Incorrect syntax near 'modify'

popped up.

After working on it for some time I've tried this one (just setting plain text to the attribute):

UPDATE #F_DATA
SET F_RESULT.modify('replace value of (Request/@guid)[1] with "myguid"');

and it's all good. BUT

UPDATE FU
SET FU.F_RESULT.modify('replace value of (Request/@guid)[1] with "myguid"')
FROM #F_DATA FU;

showed the same error again, despite being nearly the same query as the previous one.

It looks like you can only use the same table columns or variables as {sql:*} parameter for .modify(). Is there a way to do it with the FROM clause?

Upvotes: 1

Views: 209

Answers (1)

Dimon_Tools
Dimon_Tools

Reputation: 21

This problem was solved by removing the alias from updated table and removing '{' and '}' from modify.

UPDATE #F_DATA
SET F_RESULT.modify('replace value of (Request/@guid)[1] with sql:column("F_VALUE")')
FROM
    #F_DATA
        INNER JOIN
            (SELECT
                F_ID,
                F_DATA.value('(Email/@guid)[1]', 'NVARCHAR(MAX)') 'F_VALUE'
            FROM
                #F_DATA
            ) V ON #F_DATA.F_ID = V.F_ID
;

Upvotes: 1

Related Questions