問題描述

我有一個 XML 文件存儲在我的表 records 的 XML 數據類型列 data 中.

I have a XML file stored in a XML datatype column data in my table records.

表格如下所示:

create table records 
(
     id int,
     type nvarchar(28),
     data xml,
     posted datetime
)

XML 數據:

<Properties>
    <data>
        <Name>novel</Name>
        <Gender>Female</Gender>
        <Age>32</Age>
        <Salary>55k</Salary>
        <Phone>123-123</Phone>
    </data>
</Properties>

我目前正在使用以下查詢從該 XML 列中提取數據，這在 20K 條記錄中需要花費超過幾分鐘的時間.

I am currently using following query to extract data from that XML column which is taking more than minutes in 20K records.

select
    id,
    posteddate,
    CONVERT( NVARCHAR(500), data.query('data(Properties/data/Name)') ) AS Name,
    CONVERT( NVARCHAR(500), data.query('data(Properties/data/Gender)') ) AS Gender,
    CONVERT( NVARCHAR(500), data.query('data(Properties/data/Age)') ) AS Age,
    CONVERT( NVARCHAR(500), data.query('data(Properties/data/Salary)') ) AS Salary,
    CONVERT( NVARCHAR(500), data.query('data(Properties/data/Phone)') ) AS Phone
from 
    records
where 
    type = 'personnel_xml'

任何人都可以幫助解釋我如何優化這個場景，因為我需要從存儲為列的 XML 中提取 100 個這樣的元素.

Can anybody help explain how can I optimize this scenario as I need to extract 100 such elements from my XML stored as a column.

推薦答案

假設您在 XML 中有多個 .請注意，我添加了一個擴展的 XML 文件，該文件將有兩組.

Assuming you have multiple <data> within the XML. Notice I added an expanded XML file which will have two sets.

Declare @table table (id int,data xml)
Insert Into @table values (1,'<Properties><data><Name>novel</Name><Gender>Female</Gender><Age>32</Age><Salary>55k</Salary><Phone>123-123</Phone></data>
<data><Name>Another Name</Name><Gender>Male</Gender><Age>45</Age><Salary>75k</Salary><Phone>555-1212</Phone></data>
</Properties>')

;with cte as (
      Select ID
            ,RN   = Row_Number() over (Partition By ID Order By (Select Null))
            ,Data = m.query('.') 
      From   @table AS t
      Cross Apply t.Data.nodes('/Properties/data') AS A(m)
 )
Select ID
      ,RN
      ,Name   = Data.value('(data/Name)[1]'  ,'nvarchar(500)')
      ,Gender = Data.value('(data/Gender)[1]','nvarchar(500)')
      ,Age    = Data.value('(data/Age)[1]'   ,'nvarchar(500)')
      ,Salary = Data.value('(data/Salary)[1]','nvarchar(500)')
      ,Phone  = Data.value('(data/Phone)[1]' ,'nvarchar(500)')
 From  cte

退貨

ID  RN  Name            Gender  Age     Salary  Phone
1   1   novel           Female  32      55k     123-123
1   2   Another Name    Male    45      75k     555-1212

這篇關于SQL Server 查詢 XML 數據類型性能問題的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！