首页 新闻 搜索 专区 学院

存储过程查询XML

0
[已解决问题] 解决于 2008-01-24 09:53
<P>alter procedure prc_test<BR>( <BR>&nbsp;@ID nvarchar(50)<BR>)<BR>as<BR>DECLARE @tmpXML AS XML<BR>SET @tmpXML = '<BR>&lt;Categories&gt;<BR>&nbsp;&nbsp;&nbsp; &lt;CategoryInfo CategoryID ="1"&gt;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;CategoryName&gt;Beverages&lt;/CategoryName&gt; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Description&gt;Soft drinks, coffees, teas, beers, and ales&lt;/Description&gt; <BR>&nbsp;&nbsp;&nbsp; &lt;/CategoryInfo&gt;<BR>&nbsp;&nbsp;&nbsp; &lt;CategoryInfo CategoryID ="2"&gt;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;CategoryName&gt;Condiments&lt;/CategoryName&gt;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Description&gt;Sweet and savory sauces, relishes, spreads, and seasonings&lt;/Description&gt; <BR>&nbsp;&nbsp;&nbsp; &lt;/CategoryInfo&gt;<BR>&nbsp;&nbsp;&nbsp; &lt;CategoryInfo CategoryID ="3"&gt;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;CategoryName&gt;Confections&lt;/CategoryName&gt; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Description&gt;Desserts, candies, and sweet breads&lt;/Description&gt; <BR>&nbsp;&nbsp;&nbsp; &lt;/CategoryInfo&gt;<BR>&nbsp;&nbsp;&nbsp; &lt;CategoryInfo CategoryID ="4"&gt;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;CategoryName&gt;Dairy Products&lt;/CategoryName&gt; <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Description&gt;Cheeses&lt;/Description&gt; <BR>&nbsp;&nbsp;&nbsp; &lt;/CategoryInfo&gt;<BR>&lt;/Categories&gt;<BR>'</P> <P>SELECT @tmpXML.query('<BR>for $var in /Categories/CategoryInfo<BR>where $var <A>/@CategoryID</A> ="<A href="mailto:'+@ID+'">'+@ID+'</A>"<BR>return ($var)') AS test<BR>GO<BR>--exec prc_test 2</P> <P>我要按参数<U><FONT color=#006600>@ID</FONT></U>查询 @CategoryID,这里应该怎么写?<BR>where $var <A>/@CategoryID</A> ="<A href="mailto:'+@ID+'">'+@ID+'</A>"<BR></P> <P mce_keep="true">&nbsp;</P>
liaozhengcheng的主页 liaozhengcheng | 初学一级 | 园豆:100
提问于:2007-12-26 14:08
< >
分享
最佳答案
0
SELECT @tmpXML.query(' for $var in /Categories/CategoryInfo where $var /@CategoryID =sql:variable("@ID") return ($var)') AS test 还有疑问的话参考以下文章: http://msdn2.microsoft.com/zh-cn/library/ms345117.aspx
四眼蒙面侠 | 小虾三级 |园豆:504 | 2007-12-29 13:42
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册