問題描述

我有一個帶有 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

這篇關于帶有 for 循環的 xquery 中的 SQL Server 性能的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！