XQuery中的SQL Server性能与循环(SQL Server performance in

我与XML列，其持有像下面的XML格式的值的一个SQL表

<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>Local System</UserName>
      <Access>Allow</Access>
      <IsInherited>false</IsInherited>
      <ApplyTo>This object only</ApplyTo>
      <Permission>Read All Properties</Permission>
      <Permission>Read Permissions</Permission>
    </ACEInformation>
  </Dacl>
</Security>

在这里，我想从这样的XML列获取输出

[允许- >身份验证的用户- >列出内容; 读取所有属性; 阅读权限; - >此目的仅]

要做到这一点，我使用下面的for循环查询加盟值

SELECT  xmlColumn.query('for $item in/Security/Dacl/ACEInformation return("[",data($item/Access)
[1],"->",data($item/UserName)[1],"->", (for $item2 in $item/Permission return concat($item2,";")),"-
>",data($item/ApplyTo)[1],"]")').value('.','NVARCHAR(MAX)')+' ; ' From myTable

查询工作正常，但它需要太多的时间给结果，对于1000行，它走2分钟......谁能帮助我改善查询的性能？

Answer 1:

select (
       select '['+
                 A.X.value('(Access/text())[1]', 'nvarchar(max)')+
                 '->'+
                 A.X.value('(UserName/text())[1]', 'nvarchar(max)')+
                 '->'+
                 (
                 select P.X.value('(./text())[1]', 'nvarchar(max)')+';'
                 from A.X.nodes('Permission') as P(X)
                 for xml path(''), type
                 ).value('text()[1]', 'nvarchar(max)')+
                 '->'+
                 A.X.value('(ApplyTo/text())[1]', 'nvarchar(max)')+
               ']'
       from T.xmlColumn.nodes('/Security/Dacl/ACEInformation') as A(X)
       for xml path(''), type
       ).value('text()[1]', 'nvarchar(max)')
from myTable as T

Answer 2:

尝试是这样的：

DECLARE @table TABLE (ID INT, XmlCOntent XML)

INSERT INTO @Table VALUES(1, '<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>Local System</UserName>
      <Access>Allow</Access>
      <IsInherited>false</IsInherited>
      <ApplyTo>This object only</ApplyTo>
      <Permission>Read All Properties</Permission>
      <Permission>Read Permissions</Permission>
    </ACEInformation>
  </Dacl>
</Security>')

SELECT
    ID,
    Access = XACE.value('(Access)[1]', 'varchar(50)'),
    ApplyTo = XACE.value('(ApplyTo)[1]', 'varchar(50)'),
    AuthUser = XACE.value('(UserName)[1]', 'varchar(50)'),
    Perm1 = XACE.value('(Permission)[1]', 'varchar(50)'),
    Perm2 = XACE.value('(Permission)[2]', 'varchar(50)'),
    Perm3 = XACE.value('(Permission)[3]', 'varchar(50)')
FROM
    @table
CROSS APPLY
    XmlContent.nodes('/Security/Dacl/ACEInformation') AS XTbl(XACE)

给我的输出：