<content><docDeck>All you need to know before hitting the send button.</docDeck>
<docBody>
<?XML:NAMESPACE PREFIX = MSTR /><FXNT color=black face="Times New Roman, Times, serif" size=3><P>
As if a 135% return in 1999 weren't impressive enough, here's yet another technology-fund fact to make your head spin. The last year tech funds lost money, President Reagan was wrapping up his first term in the White House,
&quot;Amadeus&quot; won the Oscar for best picture, and Microsoft <MSTR:SECURITY>MSFT</MSTR:SECURITY> was still privately held. That's right: The average technology fund has finished every year since 1985 in the black. No other stock-fund category has done that. <P>Whether <B></LI></UL><P><
</docBody>
<authorHoldings></authorHoldings></content>以上为text数据列中text数据,中间删除了很多。
想要得到的结果是:
As if a 135% return in 1999 weren't impressive enough, here's yet another technology-fund fact to make your head spin. The last year tech funds lost money, President Reagan was wrapping up his first term in the White House,
&quot;Amadeus&quot; won the Oscar for best picture, and Microsoft <MSTR:SECURITY>MSFT</MSTR:SECURITY> was still privately held.取200个字符吧
<docBody>
<?XML:NAMESPACE PREFIX = MSTR /><FXNT color=black face="Times New Roman, Times, serif" size=3><P>
As if a 135% return in 1999 weren't impressive enough, here's yet another technology-fund fact to make your head spin. The last year tech funds lost money, President Reagan was wrapping up his first term in the White House,
&quot;Amadeus&quot; won the Oscar for best picture, and Microsoft <MSTR:SECURITY>MSFT</MSTR:SECURITY> was still privately held. That's right: The average technology fund has finished every year since 1985 in the black. No other stock-fund category has done that. <P>Whether <B></LI></UL><P><
</docBody>
<authorHoldings></authorHoldings></content>以上为text数据列中text数据,中间删除了很多。
想要得到的结果是:
As if a 135% return in 1999 weren't impressive enough, here's yet another technology-fund fact to make your head spin. The last year tech funds lost money, President Reagan was wrapping up his first term in the White House,
&quot;Amadeus&quot; won the Oscar for best picture, and Microsoft <MSTR:SECURITY>MSFT</MSTR:SECURITY> was still privately held.取200个字符吧
在这个基表上建立了一个分页存储过程,并返回。
如果把这个text列的全部内容否返回给程序的话,想必是非常耗资源了。
if object_id('#a') is not null
drop table #a
GODECLARE @text xml
SET @text ='<content> <docDeck>All you need to know before hitting the send button. </docDeck>
<docBody>
<?XML:NAMESPACE PREFIX = MSTR /><FXNT color=black face="Times New Roman, Times, serif" size=3><P>
As if a 135% return in 1999 weren''t impressive enough, here''s yet another technology-fund fact to make your head spin. The last year tech funds lost money, President Reagan was wrapping up his first term in the White House,
&quot;Amadeus&quot; won the Oscar for best picture, and Microsoft <MSTR:SECURITY>MSFT</MSTR:SECURITY> was still privately held. That''s right: The average technology fund has finished every year since 1985 in the black. No other stock-fund category has done that. <P>Whether <B></LI></UL><P><
</docBody>
<authorHoldings> </authorHoldings> </content> 'Select
T.i.value ('.','varchar(300)') ItemName
into #a
From @text.nodes('content/docBody') as T(i);
GO
select ItemName=cast(replace(ItemName,'<?XML:NAMESPACE PREFIX = MSTR /><FXNT color=black face="Times New Roman, Times, serif" size=3><P>','')
as varchar(200))
from #a
--result:
ItemName
As if a 135% return in 1999 weren't impressive enough, here's yet another technology-fund fact to make your head spin. The last year tech funds lost money, President Reagan was wrapping up his fir--仅供娱乐