带有 for 循环的 xquery 中的 SQL Server 性能
我有一个带有 xml 列的 sql 表，它保存的值类似于以下 xml 格式
I have one sql table with xml column, which holds the value like following xml format
<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 列中获取输出
Here, I would like get output from xml column like this
[ Allow -> Authenticated Users -> List Contents;读取所有属性；读取权限；-> 仅此对象 ]
为了实现这一点，我使用以下 for 循环查询来连接值
To achieve this, I am using following for loop query to join values
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 分钟...谁能帮助我提高此查询的性能?
The query is working fine, but it takes too much time to give result, for 1000 rows, it is taking 2 mins...can anyone help me to improve performance of this query?.
推荐答案
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
相关文章