在SQL表中的XML資料列上使用WHERE條件選擇資料

我有一張列出一些用戶詳細資訊的表格。

Data列包含使用 XML的資料。下面是表格中的一個示例。

<UserInfo xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/ComponentFramework">
<ActiveDirectoryUser>false</ActiveDirectoryUser>
<CanUpdateMasterData>false</CanUpdateMasterData>
<CanUploadFiles>false</CanUploadFiles>
<ChangePassword>false</ChangePassword>
<CustomDataPageSize>false</CustomDataPageSize>
<CustomMasterDataPageSize>false</CustomMasterDataPageSize>
<DataPageSize>100</DataPageSize>
<Disabled>true</Disabled>
<Displayname>P?l</Displayname>
<Email i:nil="true" />
<EnforcePasswordPolicy>false</EnforcePasswordPolicy>
<EnvironmentIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<GUID i:nil="true" />
<GeoLocation>
    <City i:nil="true" />
    <Country i:nil="true" />
    <CountryCode i:nil="true" />
    <Ip i:nil="true" />
    <Isp i:nil="true" />
    <Lat>0</Lat>
    <Lon>0</Lon>
    <Org i:nil="true" />
    <Query i:nil="true" />
    <Region i:nil="true" />
    <RegionName i:nil="true" />
    <Status i:nil="true" />
    <Timezone i:nil="true" />
    <Zip i:nil="true" />
</GeoLocation>
<GroupIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<HttpLink i:nil="true" />
<JobIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<LastLoggedIn>2015-06-11T19:04:44.6407074 05:30</LastLoggedIn>
<MasterDataPageSize>1000</MasterDataPageSize>
<ModifyImages>false</ModifyImages>
<QualityControl>false</QualityControl>
<QualityControlGroupId i:nil="true" />
<Review>false</Review>
<ReviewGroupId i:nil="true" />
<SecurityToken i:nil="true" />
<ShowTrackerPage>false</ShowTrackerPage>
<StatIdList xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />
<Username>&lt;new user&gt;</Username>
<Usertype>Power</Usertype>
</UserInfo>

我正在嘗試匹配其帳戶被禁用的用戶。使用下面的 sql 查詢。

select * from [ATC_Config].[dbo].[Users] where [ATC_Config].[dbo].[Users].[Data].value('/UserInfo/Disabled[1]','nvarchar(MAX)') = 'true'

但是 SSMS 給了我一個錯誤Cannot call methods on nvarchar(max)并突出顯示我的Data列。我在 SO 和 MSDN 中嘗試了一些建議，但沒有任何幫助。有人可以告訴我我做錯了什么嗎？

uj5u.com熱心網友回復：

因為您的示例 XML 包含一個默認名稱空間定義，所以您需要在valueXQuery 中或通過with xmlnamespaces.

以下是您如何使用value...

select *
from dbo.Users
where cast(Data as xml).value(N'
  declare default element namespace "http://schemas.datacontract.org/2004/07/ComponentFramework";
  (/UserInfo/Disabled)[1]',N'nvarchar(max)') = N'true';

或通過使用with xmlnamespaces：

with xmlnamespaces(default N'http://schemas.datacontract.org/2004/07/ComponentFramework')
select *
from dbo.Users
where cast(Data as xml).value(N'(/UserInfo/Disabled)[1]', N'nvarchar(max)') = N'true';

uj5u.com熱心網友回復：

您需要添加命名空間，并且需要將值轉換為xml. 如果您使用它會更容易，with因為它適用于整個查詢。

@AlwaysLearning 答案的一個稍微更有效的版本是使用exist和/text()

with xmlnamespaces (
    default N'http://schemas.datacontract.org/2004/07/ComponentFramework'
)
select *
from dbo.Users u
where cast(u.Data as xml).exist(N'/UserInfo/Disabled[text() = "true"]') = 1;

db<>小提琴

我強烈建議您首先存盤該Data列xml，因為轉換效率低下。

標籤：sql sql服务器

上一篇：除非已跳過一個，否則如何使用SQL回傳每個ID的序列中的最大值？

下一篇：'KerasClassifier'物件沒有屬性'summary'-嘗試從KerasClassifier構建的lstm模型中獲取摘要