DECLARE @ItemMessage XML

DECLARE @ItemTable TABLE(No NVARCHAR(300),zje NVARCHAR(300),yfje NVARCHAR(300),bcje NVARCHAR(300),URL NVARCHAR(300),Remark NVARCHAR(300))

SET @ItemMessage=N'<List>

 <item>

 <No>SM-JG-201303-0189</No>

  <zje>273,240</zje>

  <yfje>81,972</yfje>

 <bcje>134620</bcje>

  <URL>http://eipwf.aspirehld.com/Workflow/Page/SYS_HTPS/WF_HTPS/HT_HTPS_Default.aspx?MID=163755&amp;SID=3&amp;PID=310284</URL>

  <Remark />

 </item>

</List>'

INSERT INTO @ItemTable

(

No,

zje,

yfje,

bcje,

URL,

Remark

)

SELECT T.c.value('(No/text())[1]','NVARCHAR(300)'),

T.c.value('(zje/text())[1]','NVARCHAR(300)') ,

T.c.value('(yfje/text())[1]','NVARCHAR(300)') ,

T.c.value('(bcje/text())[1]','NVARCHAR(300)') ,

T.c.value('(URL/text())[1]','NVARCHAR(300)'),

T.c.value('(Remark/text())[1]','NVARCHAR(300)')

FROM @ItemMessage.nodes('/List/item') AS T(c)

SELECT * FROM @ItemTable

cast(XmlData as XML).value('(/List/item/No/text())[1]','NVARCHAR(300)') as No

select a.EBILLNO,

a.EMPNAME,

a.APPLYDATE,

b.HS_NAME,

replace(replace(a.SUMMARY,char(10), ''),char(13),''),

cast(c.XmlData as XML).value('(/List/item/No/text())[1]','NVARCHAR(300)') as No,

cast(c.XmlData as XML).value('(/List/item/zje/text())[1]','NVARCHAR(300)') as zje,

cast(c.XmlData as XML).value('(/List/item/yfje/text())[1]','NVARCHAR(300)') as yfje,

cast(c.XMLData as XML).value('(/List/item/bcje/text())[1]','NVARCHAR(300)') as bcje,

cast(c.XMLData as XML).value('(/List/item/URL/text())[1]','NVARCHAR(300)') as URL,

cast(c.XMLData as XML).value('(/List/item/Remark/text())[1]','NVARCHAR(300)') as BZ,

cast(d.XMLData as XML).value('(/List/item/SKRXM/text())[1]','NVARCHAR(300)') as SKRXM,

('http://……?sid=3&mid=7281&PID='+a.PID) as bxdljdz

from Ex_Bill as a

left join Ex_System_Cfg as b on(a.BILLSYSTEMID=b.HS_ID and a.DATASYSTEMID=b.SYSTEM_NAME)

left join (select * from [192.168.3.23].Flow.dbo.RepeaingTable) as c on (c.Keyword='URL' and c.ProcessID=a.PID)

left join (select * from [192.168.3.23].Flow.dbo.RepeaingTable) as d on (d.Keyword='FKXX_New' and d.ProcessID=a.PID or d.Keyword='FKXX' and d.ProcessID=a.PID)

where applyempid='zhongxun' and a.EBILLNO is not null

and status>5 and status not in(200,100,7000)

and a.APPLYDATE>'2011-01-01'

and a.HT='是'

order by a.ID desc

replace(replace(a.SUMMARY,char(10), ''),char(13),'')

select * from [192.168.3.23].Flow.dbo.RepeaingTable