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

The query suffered from a performance issue only when the results were used in an insert into a table or a join. Simply returning the values from the select in management studio was nearly instant. Prefix that with an INSERT INTO and it would take over 30 seconds for the same work.

After adding /text() such as

from @list.nodes('/List/Id/text()') as C(C)

instead of what I had:

from @list.nodes('/List/Id') as C(C)

This brought the query back to a zero-second execution even with the insert.