Speed Up XML Queries in SQL Server 2005

Question

I store all my data in on XML column in SQL Server 2005.

As more and more records are being inserted, I notice the queries are slowing down. I've tried creaeting a Primary XML Index, as well as a Secondary VALUE index and this did not do anything to help the speed.

Any tips,thoughts, or tricks that I'm missing?

Sample View that I query:

SELECT Id
, CaseNumber
, XmlTest.value('(/CodeFiveReport/ReportEvent/StartDate)[1]', 'varchar(25)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/StartTime)[1]', 'varchar(25)') as StartDate
, XmlTest.value('(/CodeFiveReport/@Status)[1]', 'varchar(10)') as [Status]
, XmlTest.value('(/CodeFiveReport/ReportEvent/Address/PatrolDistrict/@Name)[1]', 'varchar(100)') as PatrolDistrict
, XmlTest.value('(/CodeFiveReport/PrimaryUnit/@Name)[1]', 'varchar(40)') as PrimaryUnit
, XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@StreetNumber)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@StreetName)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/StreetSuffix/@Name)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@City)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/State/@Abbreviation)[1]', 'varchar(50)') + ' '  + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@ZipCode)[1]', 'varchar(50)') as Location
, XmlTest.value('(/CodeFiveReport/ReportEvent/ReportType/@Name)[1]', 'varchar(50)') as ReportType
, XmlTest.value('(/CodeFiveReport/ReportEvent/Offenses/OffenseDescription/OffenseType/@CodeAndDescription)[1]', 'varchar(50)') as IncidentType
, XmlTest as Report
, CreatedBy as UserId
, XmlTest.value('(/CodeFiveReport/PrimaryUnit/@ID)[1]', 'integer') as UnitId
, XmlTest.value('(/CodeFiveReport/PrimaryUnit/@Code)[1]', 'varchar(6)') as UnitCode
, XmlTest.value('(/CodeFiveReport/Owner/AgencyID)[1]', 'char(2)') as AgencyId   
, IsLocked
, LockedBy
, XmlTest.value('(/CodeFiveReport/VersionUsed)[1]', 'varchar(20)') as VersionUsed
FROM UploadReport
WHERE XmlTest.value('(/CodeFiveReport/Owner/AgencyID)[1]', 'char(2)') = '06'

Answer 1

Well, I was able to drastically speed up my query using two subqueries then parsing the XML from that result set.

Answer 2

Read XML Best Practices for Microsoft SQL Server 2005

The two tips I recall the most making a difference in speead are

• Use node/text() instead of just node for your xpaths.
• Try never to use ../ in your xpath expressions, as it slows it down SIGNIFICANTLY