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