DECLARE @myDoc xml
DECLARE @ProdID VARCHAR(100)
SET @myDoc = '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetBalanceResponse xmlns="http://tempuri.org/">
      <GetBalanceResult>
        <Balance xsi:nil="true" />
        <Error>AuthorizationError</Error>
      </GetBalanceResult>
    </GetBalanceResponse>
  </soap:Body>
</soap:Envelope>'

SET @ProdID =  @myDoc.value('(GetBalanceResult/Error)[1]', 'varchar(100)')
SELECT @ProdID