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

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

我有一个XML文件存储在表记录中的XML数据类型列数据中。

The table looks like this:

这个表格是这样的:

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

XML data:

XML数据:

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

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

我目前正在使用下面的查询从那个XML列中提取数据，这个XML列在20K记录中占用了超过几分钟的时间。

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'

I am very new to XML extraction in SQL Server. I guess I am not following the standard optimized approach to extract XML elements.

我对SQL Server中的XML提取非常陌生。我想我没有遵循标准的优化方法来提取XML元素。

So, can anybody help how can i optimize this scenario as I need to extract 100 such elements from my xml stored as a column.

所以，如果我需要从作为列存储的xml中提取100个这样的元素，有人能帮助我如何优化这个场景吗?

2 个解决方案

#1

To get a value out of XML in SQL Server you should use the value() Method (xml Data Type). And for untyped XML you should specify the text() node to get better performance.

要在SQL Server中从XML中获取值，您应该使用value()方法(XML数据类型)。对于非类型化的XML，应该指定text()节点以获得更好的性能。

select R.id,
       R.posted,
       R.data.value('(/Properties/data/Name/text())[1]', 'nvarchar(500)') as Name,
       R.data.value('(/Properties/data/Gender/text())[1]', 'nvarchar(10)') as Gender,
       R.data.value('(/Properties/data/Age/text())[1]', 'int') as Age,
       R.data.value('(/Properties/data/Salary/text())[1]', 'nvarchar(10)') as Salary,
       R.data.value('(/Properties/data/Phone/text())[1]', 'nvarchar(30)') as Phone
from dbo.records as R
where type = N'personnel_xml';

#2

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

假设xml中有多个。注意，我添加了一个扩展的xml文件，它将有两个集合。

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

Returns

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

#1