SQL Server 查询 XML 数据类型性能问题

2021-10-02 00:00:00 xml xquery sql-server sql-server-openxml

我有一个 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

相关文章