34,571
社区成员
发帖
与我相关
我的任务
分享
<table border="1px" cellspacing="0px">
<tr style="font-weight:bold"><td>Name</td><td>Value</td><td>Time</td></tr>
<tr><td>135888</td><td>AA1</td><td>2012/2/20 8:37:21</td></tr>
<tr><td>135888</td><td>AA2</td><td>2012/2/20 8:37:21</td></tr>
<tr style="background-color:Green"><td>135888</td><td>BB1</td><td>2012/3/31 14:40:21</td></tr>
<tr style="background-color:Green"><td>135888</td><td>BB2</td><td>2012/3/31 14:40:21</td></tr>
<tr><td>207558</td><td>XX1</td><td>2012/1/11 12:32:00</td></tr>
<tr><td>207558</td><td>XX2</td><td>2012/1/11 12:32:00</td></tr>
<tr style="background-color:Green"><td>207558</td><td>YY1</td><td>2012/2/23 17:01:00</td></tr>
<tr style="background-color:Green"><td>207558</td><td>YY2</td><td>2012/2/23 17:01:00</td></tr>
</table>
declare @tb xml
set @tb='<table border="1px" cellspacing="0px">
<tr style="font-weight:bold"><td>Name</td><td>Value</td><td>Time</td></tr>
<tr><td>135888</td><td>AA1</td><td>2012/2/20 8:37:21</td></tr>
<tr><td>135888</td><td>AA2</td><td>2012/2/20 8:37:21</td></tr>
<tr style="background-color:Green"><td>135888</td><td>BB1</td><td>2012/3/31 14:40:21</td></tr>
<tr style="background-color:Green"><td>135888</td><td>BB2</td><td>2012/3/31 14:40:21</td></tr>
<tr><td>207558</td><td>XX1</td><td>2012/1/11 12:32:00</td></tr>
<tr><td>207558</td><td>XX2</td><td>2012/1/11 12:32:00</td></tr>
<tr style="background-color:Green"><td>207558</td><td>YY1</td><td>2012/2/23 17:01:00</td></tr>
<tr style="background-color:Green"><td>207558</td><td>YY2</td><td>2012/2/23 17:01:00</td></tr>
</table>'
select
Tab.Col.value ('td[1]' , 'varchar(max)' ) as Name,
Tab.Col.value ('td[2]' , 'varchar(max)' ) as Value,
Tab.Col.value ('td[3]' , 'varchar(max)' ) as Time
from @tb.nodes ('//tr[@style="background-color:Green"]' )as Tab (Col)
/*
135888 BB1 2012/3/31 14:40:21
135888 BB2 2012/3/31 14:40:21
207558 YY1 2012/2/23 17:01:00
207558 YY2 2012/2/23 17:01:00
*/