問題描述

我有一個帶有 xml 列的 sql 表，其中包含如下 xml 的值

I have one sql table with xml column which holds the value like below xml

<Security xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Dacl>
    <ACEInformation>
      <UserName>Authenticated Users</UserName>
      <Access>Allow</Access>
      <IsInherited>false</IsInherited>
      <ApplyTo>This object only</ApplyTo>
      <Permission>List Contents</Permission>
      <Permission>Read All Properties</Permission>
      <Permission>Read Permissions</Permission>
    </ACEInformation>
    <ACEInformation>
      <UserName>Administrator</UserName>
      <Access>Allow</Access>
      <IsInherited>false</IsInherited>
      <ApplyTo>This object only</ApplyTo>
      <Permission>Read All Properties</Permission>
      <Permission>Delete</Permission>
    </ACEInformation>
    <ACEInformation>
      <UserName>Admin2</UserName>
      <Access>Allow</Access>
      <IsInherited>false</IsInherited>
      <ApplyTo>This object only</ApplyTo>
      <Permission>Read All Properties</Permission>
      <Permission>Delete</Permission>
    </ACEInformation>
    <ACEInformation>
      <UserName>Admin2</UserName>
      <Access>Deny</Access>
      <IsInherited>false</IsInherited>
      <ApplyTo>This object only</ApplyTo>
      <Permission>Read All Properties</Permission>
      <Permission>Delete</Permission>
    </ACEInformation>
  </Dacl>
</Security>

這里我的需要是，我必須查詢具有訪問:允許和權限:刪除值的所有用戶名值.為此，我正在使用以下 XQuery.

Here my need is, I have to query all UserName values who are having values Access: Allow and Permission: Delete. For that, I am using following XQuery.

select (
       select A.X.value('(UserName/text())[1]', 'nvarchar(max)')+';'
       from T.xmlColumn.nodes('/Security/Dacl/ACEInformation[Access = "Allow" and Permission = "Delete"]') as A(X)
       for xml path(''), type
       ).value('text()[1]', 'nvarchar(max)')
from myTable as T

它返回值:Administrator;Admin2 用于上述 xml.查詢工作正常..

It returns the value : Administrator;Admin2 for above xml. the query is working fine..

但在這里，我希望結果只有一個條目 Administrator 而不是 Admin2..因為 Admin2 在兩個不同的 ACEInformation 節點中同時具有 Access:Allow 和 Access: Deny 值..

But here, I expect the result only one entry Administrator not Admin2.. because Admin2 has both Access:Allow and Access: Deny values in two different ACEInformation node..

在這種情況下，我必須從結果中排除 Admin2，即使它具有 Access:Allow.

In that case, I have to exclude Admin2 from result, even though it has the Access:Allow.

誰能給我這個案例的xquery?

Can you any one give me the xquery for this case?

推薦答案

with cte as (
    select
        A.X.value('(UserName/text())[1]', 'nvarchar(max)') as UserName,
        A.X.value('(Access/text())[1]', 'nvarchar(max)') as Access
    from myTable as T
        outer apply T.xmlColumn.nodes('/Security/Dacl/ACEInformation[Permission = "Delete"]') as A(X)
)
select *
from cte as c1
where
    c1.Access = 'Allow' and
    not exists (select * from cte as c2 where c2.UserName = c1.UserName and c2.Access = 'Deny')

sql 小提琴演示

with cte as (
    select
        T.ID,
        A.X.value('(UserName/text())[1]', 'nvarchar(max)') as UserName,
        A.X.value('(Access/text())[1]', 'nvarchar(max)') as Access
    from myTable as T
        outer apply T.xmlColumn.nodes('/Security/Dacl/ACEInformation[Permission = "Delete"]') as A(X)
)
select
    T.ID,
    stuff(
        (
            select ',' + c1.UserName
            from cte as c1
            where
              c1.ID = T.ID and c1.Access = 'Allow' and
              not exists (select * from cte as c2 where c2.ID = T.ID and c2.UserName = c1.UserName and c2.Access = 'Deny')
            for xml path(''), type
        ).value('.', 'nvarchar(max)')
    ,1,1,'')
from myTable as T