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

• jerry-621596

  SSCertifiable

  Points: 6096

  More actions

  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!

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  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

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  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