TSQL where on xml 数据类型 [英] TSQL where on xml data type

问题描述

如何在 Sql Server 2005 中对 xml 数据类型执行 where 子句命令?

How does one execute where clause command on a xml data type in Sql Server 2005 ?

<User UserId="1" UserName="x">
<User UserId="2" UserName="y">

查询

SELECT XmlColumn from Table where XmlColumn.query('/User[@UserId'+ @dynamicValue +']')

预期输出

获取属性 UserId = 输入变量的所有用户标签

Intended Output

Get all the user tags where the attribute UserId = input variable

推荐答案

declare @T table
(
  XMLColumn xml
)

insert into @T values ('<User UserId="1" UserName="x"/>')
insert into @T values ('<User UserId="2" UserName="y"/>')

declare @UserID int
set @UserID = 1

select XMLColumn
from @T
where XMLColumn.exist('/User[@UserId = sql:variable("@UserID")]') = 1

结果:

XMLColumn
---------------------------------
<User UserId="1" UserName="x" />

这篇关于TSQL where on xml 数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！