SQLTeam.com | Weblogs | Forums

Update a value from one column to a part of xml in another column


#1

Hi Peeps,
I have a table in sql with two columns.
Email (text)
Staticxml (xml)

Email contains different email address in every row.
Static xml have a tag called email@domain.com Which is static in every row.

I am looking to replace email@domain.com in every row with the email address from email column.

How can I achieve this?

Thanks


#2

Hi

I have done it like this
Please correct me if I am wrong
Thanks

Data Script

Summary
USE adventureworks2012 

go 

DROP TABLE dbo.[harish_temp]; 

go 

CREATE TABLE dbo.[harish_temp] 
  ( 
     email    VARCHAR(500) NULL, 
     xmlemail XML NULL 
  ); 

go 

Declare @XmlStr XML;
SET @XmlStr='<emails>
 <email>
       <FirstName>abc@domain.com</FirstName>    
  </email>  
</emails>';

INSERT INTO dbo.[harish_temp] 
SELECT 'def@gmail.com', 
       [Table].[Column].value('FirstName [1]', 'varchar(50)') AS ' email ' 
FROM   @xmlStr.nodes('/ emails / email') AS [Table]([Column]) 

INSERT INTO dbo.[harish_temp] 
SELECT 'harry@gmail.com', 
       [Table].[Column].value('FirstName [1]', 'varchar(50)') AS ' email ' 
FROM   @xmlStr.nodes('/ emails / email') AS [Table]([Column]) 

INSERT INTO dbo.[harish_temp] 
SELECT 'java@gmail.com', 
       [Table].[Column].value('FirstName [1]', 'varchar(50)') AS ' email ' 
FROM   @xmlStr.nodes('/ emails / email') AS [Table]([Column]) 

INSERT INTO dbo.[harish_temp] 
SELECT 'red@gmail.com', 
       [Table].[Column].value('FirstName [1]', 'varchar(50)') AS ' email ' 
FROM   @xmlStr.nodes('/ emails / email') AS [Table]([Column]) 

go 

SELECT * 
FROM   dbo.[harish_temp] 

go

Query Script

Summary
SELECT * 
FROM   dbo.[harish_temp] 

go 

UPDATE a 
SET    a.xmlemail = b.[email] 
FROM   dbo.[harish_temp] AS a 
       INNER JOIN dbo.[harish_temp] AS b 
               ON a.email = b.email; 

SELECT * 
FROM   dbo.[harish_temp] 

go


#3

Not really.

Think of having an exact xml in sql column. The query should find just the email@domain.com and replace just that with the email address from the email column.


email@domain.com