SQL 2005 View Xml Data

I have a table called People one of the columns is an xml field called "properties". I've used this to store random information about each person basically allowing people to store any extra fields that are added in the future without a database redesign. Not all people will have the same elements in their xml.

CREATE TABLE [dbo].[Person]( 
 [PersonID] [bigint] IDENTITY(1,1) NOT NULL, 
 [PersonType] [nvarchar](50) NULL, 
 [Title] [nvarchar](5) NULL, 
 [Forename] [nvarchar](60) NULL, 
 [Surname] [nvarchar](60) NULL, 
 [Company] [nvarchar](60) NULL, 
 [Properties] [xml] NULL 
) 

An example of the xml is:

<PropertyList xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <Property Name="Class">Class A</Property> 
  <Property Name="CarRegistration">123456</Property> 
  <Property Name="MedicalNotes">None</Property> 
</PropertyList> 

I have several old access frontend databases that need to access this data my plan was to create a view for each frontend which formated the table to match its orginal layout. Saving us the effort of re-developing the access frontends as they are due to be removed at some point soon. However in my new design a few fields are stored inside the XML pr开发者_如何学Coperties. Is there anyway i can create a updateable view of the table with one or more columns saving back to the xml field.

I might for example want to create a view of the table called "people_students" which only returns all records where PersonType="Student" and one of the columns is called "Class" which is currently stored in the xml.

The short answer is no.

However, you might be able to kludge something together with temporary tables (as you mentioned above, assuming that you meant tables to be removed at some point in the future) and triggers. You'd need a trigger on your XML column that fired whenever someone updated that column, and you'd need a trigger on your non-permanent table that updated the XML.

Unfortunately, it's ugly.

What do you think about this approach?

Create an EAV table. Populate the table with shreaded XML when the associated record data is supplied to the user. Allow the user to Insert, Update or Delete from the EAV table. When the user navigates away from the record or presses save create XML from the EAV table and update the XML column in the DB. The EAV could be left or removed depending on the volumn of data and frequency of user updates.