CODEWITHSUNDEEP
sql-serverxmlxquerysql-server-openxml
OmGanesh
OmGanesh

Reputation: 1062

SQL Server query XML datatype performance issue

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

The table looks like this:

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

XML data:

<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.

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'

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.

Upvotes: 1

Views: 8035

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81960

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

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

Upvotes: 4

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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.

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';

Upvotes: 3

Related Questions