SQL Server 2005 - 160 bit Encryption/Decryption function

[CVMichael]

Just in case you don't realize the potential of this, you can basically encrypt an entire table by converting the table to XML, like in this example:

DECLARE @DataTable XML
DECLARE @EncDataTable VARBINARY(MAX)
DECLARE @DecDataTable XML

-- Convert the table to XML
SET @DataTable = (
	SELECT *
	FROM dbo.BackupUsers AS Data -- table to encrypt
	FOR XML AUTO, ELEMENTS
)

SELECT @DataTable, DATALENGTH(@DataTable)

-- Encrypt
SET @EncDataTable = dbo.fn_Encrypt(CAST(@DataTable AS VARBINARY(MAX)), 'test password')
SELECT @EncDataTable, DATALENGTH(@EncDataTable)

-- Decrypt
SET @DecDataTable = CAST(dbo.fn_Encrypt(@EncDataTable, 'test password') AS XML)
SELECT @DecDataTable, DATALENGTH(@DecDataTable)

-- select the data from the XML table
SELECT data.value('UserID[1]', 'INT') AS UserID
	, data.value('UserName[1]', 'varchar(50)') AS UserName
	, data.value('Admin[1]', 'bit') AS [Admin]
	, data.value('DateCreated[1]', 'datetime') AS DateCreated
	, data.value('Active[1]', 'bit') AS Active
FROM @DecDataTable.nodes('/Data') R(data)