I think you are just missing the namespace reference as part of the XPath... Try this:
--now start shredding the xml
WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)
SELECT *,
RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/sp:Message/sp:Body/sp:RefillRequest/sp:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')
FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]
)MyAlias