Declare @xml xml
Set @xml = '<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
  <Code>-8408</Code>
  <Description>Target service 'ProdWebOracle' does not support contract 'DevWebOracle_Contract'.</Description>
</Error>

Select @xml.value('(/Error/Description/text())[1]', 'nvarchar(255)' )

select cast(@xml.query(N'declare namespace
           brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
               (/brokerns:Error/brokerns:Description/text())[1]') as nvarchar(255))

Select @xml.value('(/*:Error/*:_Description/text())[1]', 'nvarchar(255)' )