sql 解析text的XML数据

//**创建临时表,用于存储批量准备的供应商信息 准入日期是按部门经理审批的时间**//
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#Suppliertemp') and type='U')
  drop table #Suppliertemp
create table #Suppliertemp
(
   ProjectId varchar(50),
   Supplierid varchar(50),
  SuplierAllowdate datetime
)
DECLARE Supplier_Cursor CURSOR FOR
Select  ProjectID,convert(xml,ListData)as Supplierid,DeptDate from AFPProjectSupplierBatchAllow  where Status='EndAudit';
OPEN Supplier_Cursor;
Declare @Sprojectid varchar(12)
declare @supplierid xml
declare @sdeptdate datetime
FETCH NEXT FROM Supplier_Cursor into @Sprojectid,@supplierid,@sdeptdate;
WHILE @@FETCH_STATUS = 0
   BEGIN
      insert into #Suppliertemp  select @Sprojectid, L.n.value('(.)','NVARCHAR(MAX)'),@sdeptdate FROM  @supplierid.nodes('/List/Item/@Supplier_ID')L(n)
      FETCH NEXT FROM Supplier_Cursor into @Sprojectid,@supplierid,@sdeptdate;
   END;
CLOSE Supplier_Cursor;
DEALLOCATE Supplier_Cursor;
/*将单个准入表的信息插入到临时表*/
insert into #Suppliertemp  select ProjectID,SupplierID,DeptDate from AFPProjectSupplierAllow


--select supplierid,min(suplierallowdate)as allowdate from #suppliertemp  where year(suplierallowdate)!=9999 group by supplierid

--select Supplier_AllowDate,* from Supplier  where Supplier_Grade='Project'
select * from  #supplierTemp

--select * from Supplier where  Supplier_Grade='Project'

---select a.Supplier_AllowDate,* from Supplier a
---left join (select supplierid,min(suplierallowdate)as allowdate from #suppliertemp  where year(suplierallowdate)!=9999 group by supplierid
---)b on a.supplier_id=b.supplierid
---where  a.Supplier_Grade='Project'

update Supplier set Supplier_AllowDate=b.allowdate  
from Supplier,
 (select supplierid,min(suplierallowdate)as allowdate from #suppliertemp  where year(suplierallowdate)!=9999 group by supplierid)b
 where   Supplier_id=b.supplierid
and   Supplier_Grade='Project'


  drop table #Suppliertemp