SQL2005 XML 的例子

CONTEXT字段类型为XML

 

insert into T (CONTEXT) values ('<tj><yxrq>2007-12-31</yxrq><zdsl>500</zdsl></tj>')
insert into T (CONTEXT) values ('<tj><yxrq>2007-12-31</yxrq><zdsl>600</zdsl></tj>')
insert into T (CONTEXT) values ('<CO><tj><ID>11</ID><yxrq>2007-12-31</yxrq><zdsl>500</zdsl></tj></CO>')

insert into T (CONTEXT) values ('<CO><tj><ID>12</ID><yxrq>2007-12-31</yxrq><zdsl>600</zdsl></tj></CO>')

select CONTEXT.query('//zdsl') FROM T  WHERE CONTEXT.value('(//zdsl)[1]','decimal')=600
select CONTEXT FROM T  WHERE CONTEXT.exist('//ID')=1

select CONTEXT FROM T  WHERE CONTEXT.value('(//ID)[1]','decimal')=12

 

ID写成id，就什么都查不到。

 

结果

<zdsl>600</zdsl>
<zdsl>600</zdsl>

(2 行受影响)

 

CONTEXT
<CO><tj><ID>11</ID><yxrq>2007-12-31</yxrq><zdsl>500</zdsl></tj></CO>
<CO><tj><ID>12</ID><yxrq>2007-12-31</yxrq><zdsl>600</zdsl></tj></CO>

(2 行受影响)

 

CONTEXT
<CO><tj><ID>12</ID><yxrq>2007-12-31</yxrq><zdsl>600</zdsl></tj></CO>

(1 行受影响)