SQL Server 2005 Querying an XML Data Column

Question

I have a table called Peoplewith data type columns xmlcalled properties. I used this to store random information about each person, basically allowing people to store additional data that will be added in the future without redesigning the database. 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
)

Xml example:

<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: I can’t find a SQL query that will allow me to get a list of records that match the criteria stored in xml.

For example, how to get all the records, where Class="Class A". I 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 one-point (or empty sequence)”, and I'm not quite sure what happened.

, , - . , , , . XML. XML, xml i.e, , . , . XML, , , .

.

Answer 1

XQuery :

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

?

: - /PropertyList/Property[@Name="Class"], , [1] ( ) ( ) , NVARCHAR(50).

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

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

2:, - , !; -)

- :

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'

"" XML . , ?