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
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
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.