Thanks for the advice guys, I was still getting an encoding conversion error and in the end used the following to sort it out - it seems removing the encoding tag from within the XML string itself did the trick quite nicely.
SELECT CAST(REPLACE(CAST(Content AS NVARCHAR(MAX)), 'encoding="utf-16"', '') AS XML) AS ContentXML
However having struggled for a few hours trying to work with the data, it seems my local workstation and 2008R2 just doesn't have enough juice to get the job done.
I thought about using the 2000 server to get the job done as it has a lot more resource available on its stack. I've tried the following and its working great:
DECLARE @i INT, @xml VARCHAR(8000)
SELECT TOP 1 @xml = REPLACE(CONVERT(VARCHAR(8000), Content),'encoding="utf-16"','') FROM UPLSalesP.dbo.RSSDocument
EXEC sp_xml_preparedocument @i OUTPUT, @xml
SELECT * FROM OPENXML(@i, N'/UPLFolder/UPL/ApplicationData/LoanRequirements')
EXEC sp_xml_removedocument @i
However this is providing me a node-by-node rowset, when in reality all I want is the value of one node, for every record in the table.
Can I use OPENXML to read the xml string from every row in the table and pull a value from a specific node?