SQL Server 2005 查询XML列数据

Question

SQL Server 2005 查询XML列数据

sql-serverxmlsql-server-2005

3

我有一个名为People的表，其中包含一个数据类型为xml的列，名为properties。我使用它来存储每个人的随机信息，基本上允许人们存储未来添加的任何额外数据，而无需重新设计数据库。并非所有人的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 的示例是：

<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>

首先，我找不到一条SQL查询语句，可以获取与存储在XML中的条件匹配的记录列表。

例如，如何获取所有Class="Class A"的记录。我尝试过以下查询：

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

我知道这是错误的，但我遇到了“需要单例（或空序列）”错误，我不确定出了什么问题。

另一个问题是，我已将多个旧数据库合并成一个人员列表，但旧数据库前端仍然需要访问其数据。我的计划是为每个数据库前端创建一个视图，具有特定于其需求的布局，所有视图都链接回主要人员表。但是，其中一些字段现在存储在XML中。是否有任何方法创建视图以更新XML而不查看XML，即使它看起来和操作起来就像其他表上的视图一样。我希望我解释得正确。对于每个视图，我将有一个特定的XML属性集，我需要他们编辑，并且所有记录都将拥有它们，因此不会那么随机。

谢谢任何帮助。

- Jammy

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- marc_s · Accepted Answer

你的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“分解”为列。这是您要寻找的内容吗？