Read XML Using SQL

Select  1 as number,transactionid,AgencyId  as AgencyId,ResponseXML,
'asdfadf asdfsdf asdf sdf asfasdfasd asdfasd sdfasfas sdfasfasf asdfasdfasd asdfasf' as error,
ROW_NUMBER() over(order by transactionid) AS Row
into #temp 
from wsaudit
where wseventtype = 10 and producttypeid = 1 and  createdon between '2014-01-02' and '2014-01-03'
and transactionid is not null and transactionid <> '' and status=0
declare @Error varchar(100)
DECLARE @Names VARCHAR(max)  
Declare @hotels XML 
declare @max int
declare @count int
select @max = max(Row) from #temp
set @count=1;
if @max is not null and @max>0
BEGIN
     while @count < =@max
         begin
          select @Names=ResponseXML from #temp where Row=@count          
   if @Names is not null
    begin
set @Names = '<newData>' + @Names  +'</newData>' 
set @hotels = @Names
DECLARE @docHandle1 int 
EXEC sp_xml_preparedocument @docHandle1 OUTPUT, @hotels
select  @Error=Error
FROM OPENXML (@docHandle1,'/newData/ResponseDetails',2) 
with 
(Error varchar(100) './ErrorMessage'  
)        
if @Error is not null
begin
    update #temp set error= @Error where row=@count
end          
  end
set @count=@count+1;
      end
end
select * from #temp
drop table #temp


-----------------------------------------------------------------------------

<EncryptedResponseDetails>
<mtrixID>4OWUfYETzsRsRre2A9OcXw==</mtrixID>
<status>3ALh/V  Zoy46548c8BSlw==</status>
<ErrorMessage>UxqyxjlVomSZljJJGwYHHbRCw OC6 swGCt2qdoS3q0tNU5W5n5SHtNc/DNStVGu</ErrorMessage>
<TransactionId>X8YAxOxAh6HhdCite7ySCg==</TransactionId>
<paidamt>0Ad9/RiDxHh4K9ELPD5x/Q==</paidamt>
</EncryptedResponseDetails>
<ResponseDetails>
<mtrixID>1109944</mtrixID>
<status>1</status>
<ErrorMessage>Transaction failed due to mtrxid is duplicate</ErrorMessage>
<TransactionId>0</TransactionId>
<paidamt>9825</paidamt>
</ResponseDetails>