Lowell (5/1/2014)
i've tried sys.fn_cdc_hexstrtobin to convert the string to varbinary, but it truncates a large portion of the value.maybe someone else can take the ball and run with it form here?
Lowell, you where almost there! Changed the code to modify and store the encrypted secret, then decrypt in the select. This code has everything needed for either storing the encrypted secret within the xml or for ripping it out and storing it elsewhere.:
😎
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = 'AES128SecureSymmetricKey')
BEGIN
CREATE SYMMETRIC KEY AES128SecureSymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
END
-- must open the key if it is not already
OPEN SYMMETRIC KEY AES128SecureSymmetricKey
DECRYPTION BY PASSWORD = N'StrongP@ssw0rd!';
--sample data
IF Object_id('[dbo].[XMLExample]') IS NOT NULL
DROP TABLE [dbo].[XMLExample]
GO
CREATE TABLE [dbo].[XMLExample]
(
[XID] INT IDENTITY(1, 1) NOT NULL,
[XData] XML NULL,
CONSTRAINT [PK__XMLExamp__DB7757004E739D3B] PRIMARY KEY CLUSTERED ([XID] ASC)
)
DECLARE @XML XML;
INSERT INTO dbo.XMLExample(XData)
VALUES
('<ROOT>
<Customers>
<CustomerId>1111</CustomerId>
<CompanyName>Sean Chai</CompanyName>
<Secret>##PASSWORD001</Secret>
<City>NY</City>
</Customers>
</ROOT>'),
('<ROOT>
<Customers>
<CustomerId>1112</CustomerId>
<CompanyName>Tom Johnston</CompanyName>
<Secret>##PASSWORD002</Secret>
<City>LA</City>
</Customers>
</ROOT>'),
('<ROOT>
<Customers>
<CustomerId>1113</CustomerId>
<CompanyName>Institute of Art</CompanyName>
<Secret>##PASSWORD003</Secret>
<City>MIA</City>
</Customers>
</ROOT>');
;WITH XML_SRC AS
(
SELECT
XE.XID
,CUS.TID.value('.[1]','INT') AS CustomerId
,SEC.RET.value('.[1]','NVARCHAR(MAX)') AS Secret
,CONVERT(NVARCHAR(MAX),EncryptByKey(Key_GUID('AES128SecureSymmetricKey'),SEC.RET.value('.[1]','NVARCHAR(MAX)')),2) As EncryptedSecret
FROM XMLExample XE
OUTER APPLY XE.XData.nodes('ROOT/Customers') AS CUST(OMER)
OUTER APPLY CUST.OMER.nodes('CustomerId') AS CUS(TID)
OUTER APPLY CUST.OMER.nodes('Secret') AS SEC(RET)
)
UPDATE XT
SET XData.modify('
replace value of (/ROOT/Customers/Secret[1]/text())[1]
with sql:column("XS.EncryptedSecret") ')
FROM XML_SRC XS
INNER JOIN dbo.XMLExample XT
ON XS.XID = XT.XID;
SELECT
XE.XID
,CUS.TID.value('.[1]','INT') AS CustomerId
,SEC.RET.value('.[1]','NVARCHAR(MAX)') AS Secret
FROM XMLExample XE
OUTER APPLY XE.XData.nodes('ROOT/Customers') AS CUST(OMER)
OUTER APPLY CUST.OMER.nodes('CustomerId') AS CUS(TID)
OUTER APPLY CUST.OMER.nodes('Secret') AS SEC(RET);
SELECT
XE.XID
,CUS.TID.value('.[1]','INT') AS CustomerId
,SEC.RET.value('.[1]','NVARCHAR(MAX)') AS Secret
,CONVERT(NVARCHAR(MAX),DECRYPTBYKEY(CONVERT(VARBINARY(MAX),SEC.RET.value('.[1]','NVARCHAR(MAX)'),2))) AS Secret2
FROM XMLExample XE
OUTER APPLY XE.XData.nodes('ROOT/Customers') AS CUST(OMER)
OUTER APPLY CUST.OMER.nodes('CustomerId') AS CUS(TID)
OUTER APPLY CUST.OMER.nodes('Secret') AS SEC(RET);
-- close and drop the key
CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;
--DROP SYMMETRIC KEY AES128SecureSymmetricKey;