I have a table:
CREATE TABLE [dbo].[Incident](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[IncidentNo] [varchar](20) NOT NULL,
[IncidentDate] [datetime] NULL,
[CreatedDate] [datetime] NULL,
[AssignedToName] [nvarchar](250) NULL,
[InsertedDateTime] [datetime] NULL,
[IncidentData] [xml] NULL,
CONSTRAINT [PK_Incident] PRIMARY KEY CLUSTERED
The table holds data that is created when a user fills in a form (of which there are several templates). The IncidentData column contains the whole form stored as XML. This xml is untyped, and the structure changes depending on what parts of the form the user completes. The client wishes to report on the content contained in this form. We could have up to 1 million forms completed every year, and we are to store up to 7 years of data. We need to report across the whole period. I need some method of exposing the data to the reports, in a way that is performant. An original idea was the shred the data/xml fields that are to be reported on into separate tables. However, the user will want to add additional columns to reports, and potentially report retrospectively. Therefore, everytime they wanted to do this, we would have to add a new column to the table, and then have a process to populate the data retrospectively. Therefore, we needed a solution that was more flexible.
The idea is to build a view on the columns to shred the data into a relational table, and then the reporting procedures would reference this view. I have been looking at the nodes() function, but have no idea whether this would suit our requirement.
The xml structure is very simplistic:
<confirmationmessage value="Confirmation Message" />
<AllOtherElements value="another value" />
So we shall any number of elements contained in the 2 opening elements. It doesn't get any more complex than that. The only thing is that the nested elements will not be consistent across all the records in the xml column.
Confused dot com