SQL Server 2005 Querying XML column Data

I have a table called People with a columns of datatype xml called properties. I've used this to store random information about each person basic开发者_StackOverflow社区ally allowing people to store any extra data 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>

First question is I can't seem to find a SQL query that will allow me to get a list of records that match criteria stored in the xml.

For example how would i get all records where the Class="Class A". I've tried :

SELECT 
    PersonID, 
    Properties.value('/PropertyList/Property[@Name="Class"][1]','nvarchar(50)') 
FROM Person

I know this is incorrect but I get the error "requires a singleton (or empty sequence)" and I'm not quite sure whats gone wrong.

And second side question is I've combined several older databases into a single person list however the old database frontends still need to access their bit of the data. My plan was to create a view for each database frontend with a layout specific for its needs all linking back to the main people table. However some of their fields are now stored in the XML. Is there any way of creating a view to update the XML without seeing the xml i.e. so it looks and acts just like a view on any other table. I hope i explained that correctly. For each view I will have a specific set of XML properties I need them to edit and all records will have them so its not so random.

Thanks for any help.

Your XQuery should be:

SELECT PersonID, 
       Properties.value('(/PropertyList/Property[@Name="Class"])[1]','NVARCHAR(50)') 
FROM dbo.Person

Does that help??

Update: to make it clearer for others - I've added parenthesis around the /PropertyList/Property[@Name="Class"] expression, so that this will evaluate to potentially a list of values, and then the [1] after the parenthesis will select the first (and most often only) value (as a singleton) of that list so it can be converted to a NVARCHAR(50) string.

value('(/PropertyList/Property[@Name="Class"])[1]','NVARCHAR(50)') 
       !                                     !

is NOT the same as

value('/PropertyList/Property[@Name="Class"][1]','NVARCHAR(50)') 

Update 2: if you want to create a view - sure, no reason not to! ;-)

You could definitely create something like:

CREATE VIEW dbo.YourViewName
AS
    SELECT
        PersonID, PersonType, Title, 
        ForeName, Surname, Company,
        Properties.value('(/PropertyList/Property[@Name="Class"])[1]','NVARCHAR(50)') AS 'Class',
        Properties.value('(/PropertyList/Property[@Name="CarRegistration"])[1]','NVARCHAR(50)') AS 'CarRegistration',
        Properties.value('(/PropertyList/Property[@Name="MedicalNotes"])[1]','NVARCHAR(50)') AS 'MedicalNotes'

from your table and "break up" the XML into columns on your view. Is that what you're looking for??