Post reply

Using XQuery modify 'replace value of' using a table column value as the new value

  • April 17, 2015 at 9:53 am

    #299915

    I have used the XQuery.modify('replace value of...') statement before using a static string value as the replacement value.

    Now I need to use the value of a column in a related table as the new value and I am having a bit of trouble with it. This is the statement I am trying to use, but it isn't working out.

    UPDATE sts

    SET MailerID = nm.newmailer,

    ExtendedXml.modify('replace value of (//SelectedLetterKeyTO/letter/lettercode/text())[1] with sql:column("nm.newmailer")'),

    CreatedUTC = GETUTCDATE()

    FROM ipztech..PROD10194SmpTemplateStepsToTest sts

    INNER JOIN ipzTech..PROD10194_Mailers nm ON sts.MailerID = nm.oldmailer

    I have been researching this for a couple of days and found the sql:column() function, assuming it would do what I want to do. But I'm thinking I may have misunderstood what it does or how it is used.

    Does anyone have an example of a modify(replace value of) statement that uses a value from a related table?

    Thanks!

  • April 17, 2015 at 12:33 pm

    #1791770

    DDL and sample data?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • April 17, 2015 at 1:02 pm

    #1791774

    Ok, I put together some DDL and sample data... I screwed up the aliases a little compared to what you have but this should still suffice. What you are doing should be getting you the correct result but I don't know what you are expecting. Note my comments and how the XML data changes after each update...

    USE tempdb;

    IF OBJECT_ID('tempdb.dbo.sts') IS NOT NULL DROP TABLE dbo.sts;

    IF OBJECT_ID('tempdb.dbo.table2') IS NOT NULL DROP TABLE dbo.table2;

    GO

    CREATE TABLE dbo.sts(MailerID int, ExtendedXml XML not null, CreatedUTC datetime NULL);

    CREATE TABLE dbo.table2(oldmailer int, NewMailer int not null);

    GO

    INSERT dbo.sts (MailerID, ExtendedXml) VALUES

    (1,'<x><SelectedLetterKeyTO><letter><lettercode>old value1</lettercode></letter></SelectedLetterKeyTO></x>'),

    (2,'<x><SelectedLetterKeyTO><letter><lettercode>old value2</lettercode></letter></SelectedLetterKeyTO></x>'),

    (3,'<x><SelectedLetterKeyTO><letter><lettercode>old value3</lettercode></letter></SelectedLetterKeyTO></x>');

    INSERT dbo.table2 VALUES (1,11),(2,12),(3,13);

    -- BEFORE

    SELECT * FROM dbo.sts;

    -- UPDATE 1: USING STATIC VALUE

    UPDATE dbo.sts

    SET ExtendedXml.modify('replace value of (//SelectedLetterKeyTO/letter/lettercode/text())[1] with "NEW STATIC VALUE"');

    -- AFTER UPDATE 1:

    SELECT * FROM dbo.sts;

    -- UPDATE 2: Getting value from another table

    UPDATE dbo.sts

    SET MailerID = NewMailer,

    ExtendedXml.modify('replace value of (//SelectedLetterKeyTO/letter/lettercode/text())[1] with sql:column("NewMailer")'),

    CreatedUTC = GETUTCDATE()

    FROM dbo.table2 nm

    JOIN sts on sts.MailerID = nm.oldmailer

    -- AFTER UPDATE 2:

    SELECT * FROM dbo.sts;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply