i found this interesting, and am about 90% there with an example solution.

my problem is if there is a text representation of a binary string in the xml, how do i convert it to varbinary, so i can feed it to the decrypt function?

i took a string and used the encryption to get the binary value, and modified the xml accordingly and put it in the <Secret> xml tag.

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?

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;

SELECT @XML = '<ROOT>

<Customers>

<CustomerId>1111</CustomerId>

<CompanyName>Sean Chai</CompanyName>

<Secret>0x00DC079B85ED3B4299B9276648A092B201000000ABA3095D0DD8D6DCC34DF92A7EB24E067024A4653F1203B9BA00A946CD97D02493AD7DE0312F2675A978AFA8ABE31A029198F5BBE93AFA7D2C1AA9635D458BA6</Secret>

<City>NY</City>

</Customers>

<Customers>

<CustomerId>1112</CustomerId>

<CompanyName>Tom Johnston</CompanyName>

<Secret>0x00DC079B85ED3B4299B9276648A092B2010000001ABF8DC5B30323745A5D9F6314478607C0795A0E6009F26C65058F9A0FDE768BA5E9360266E5FF6F7B58F91758E59CEA97D23BC8425289F09194A1D504FDA34E</Secret>

<City>LA</City>

</Customers>

<Customers>

<CustomerId>1113</CustomerId>

<CompanyName>Institute of Art</CompanyName>

<Secret>0x00DC079B85ED3B4299B9276648A092B2010000001A0A49247F58956CF3E5A197551AE38FA0541526B317E351682774738795577FC2A91648B4A9FE7F7938EF0EC56E4EE4AE389F5AF5E40B3859960A3E69AEC3E78060139C2228598A73F66DB1EC4D5B17</Secret>

<City>MIA</City>

</Customers>

</ROOT>';

INSERT INTO XMLExample

(XData)

SELECT @XML

SELECT --R.Node.query('.'),

R.Node.query('.').value('(/Customers/CustomerId/.)[1]', 'varchar(100)') AS CustomerID,

R.Node.query('.').value('(/Customers/CompanyName/.)[1]', 'varchar(100)') AS CompanyName,

R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varchar(8000)') AS CompanySecret,

R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varbinary(8000)') AS CompanySecretBin, --does not work:

sys.fn_cdc_hexstrtobin( R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varchar(8000)')) As BinVal,

DECRYPTBYKEY(sys.fn_cdc_hexstrtobin( R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varbinary(8000)'))) As [Decrypted?],

--EncryptByKey(Key_GUID('AES128SecureSymmetricKey'), R.Node.query('.').value('(/Customers/Secret/.)[1]', 'varchar(100)'))As EncryptedName,

R.Node.query('.').value('(/Customers/City/.)[1]', 'varchar(100)') AS CityName

--into #t

FROM XMLExample

CROSS APPLY xData.nodes('/ROOT/Customers') R(Node);

-- close and drop the key

CLOSE SYMMETRIC KEY AES128SecureSymmetricKey;

--DROP SYMMETRIC KEY AES128SecureSymmetricKey;