我在xml列中有以下xml数据
<LoyaltyInfo ServerDate="2056-12-12">
<Accounts>
<Acc ID="1" Value="2.750" UpToDate="2014-05-13 18:38:00" />
<Acc ID="2" Value="2.750" UpToDate="2014-05-13 18:38:00" />
<Acc ID="3" Value="141.06" UpToDate="2014-05-22 12:53:00" />
<Acc ID="6" Value="924.42" UpToDate="2014-06-01 16:53:00" />
<Acc ID="7" Value="2895" UpToDate="2014-05-13 18:38:00" />
</Accounts>
</LoyaltyInfo>
我想写一个SQL查询说WHERE Acc ID = x,在同一行Acc值= y
我尝试了以下,但我得到错误
SELECT *
FROM TableName
WHERE xmlData.exist('/LoyaltyInfo/Accounts/Acc[@ID="20"]') = 1
AND xmlData.value('/LoyaltyInfo/Accounts/Acc/@Value','integer') = 0
SELECT *
FROM TableName
WHERE xmlData.exist('/LoyaltyInfo/Accounts/Acc[@ID="20"]') = 1
AND xmlData.value('/LoyaltyInfo/Accounts/Acc[@Value]','integer') = 0
尝试使用fn.min()
函数来模拟AND
操作符:
select *
FROM T
WHERE xmldata.value('fn:min((LoyaltyInfo/Accounts/Acc/@ID="3",
LoyaltyInfo/Accounts/Acc/@Value="141.06"))','bit')=CAST(1 as bit)
<<p> SQLFiddle演示/kbd> 供其他想看未来的人参考
For 1条件
select xmldata.query('for $loyaltyinfo in /LoyaltyInfo/Accounts
let $id :=$loyaltyinfo/Acc/@ID
let $value := $loyaltyinfo/Acc/@Value
where $id = "19" and $value = "141.06"
return $loyaltyinfo
') as [Valid Data]
FROM T
对于2个条件
select xmldata.query('for $loyaltyinfo in /LoyaltyInfo/Accounts
let $id :=$loyaltyinfo/Acc/@ID
let $value := $loyaltyinfo/Acc/@Value
where ($id = "19" and $value = "141.06") and ($id = "20" and $value = "0.00")
return $loyaltyinfo
') as [Valid Data]
FROM T