Mark-101232 (5/8/2013)
Quick 'n dirty solution
SELECT *,RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/*:Message/*:Body/*:RefillRequest/*:RxReferenceNumber/text()) [1]', 'NVARCHAR(256)')
FROM (SELECT ID,CONVERT(xml,message)As ConvertedXML FROM [surescripts_msg_import]
)MyAlias
Nice one there Mark.. I just came up with this, but i prefer yours for readability.. I'll have to remember your one. 🙂
--now start shredding the xml
WITH XMLNAMESPACES ('http://www.surescripts.com/messaging' AS sp)
SELECT *,
RxReferenceNumber = MyAlias.ConvertedXML.value(N'(/Message/Body/RefillRequest/RxReferenceNumber/text(),
(/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