在 SQL Server 中访问 XML 列中的数据的首选方法

Question

背景

最近我开始更多地使用 XML 作为 SQL Server 2005 中的列。在昨天的一段停机时间内，我注意到我使用的两个链接表确实很碍事，这让我感到无聊得流泪了为几个连接编写更多支持结构代码。

为了实际生成这两个链接表的数据，我将两个 XML 字段传递给存储过程，该存储过程写入主记录，将两个 XML 变量分解为 @tables 并将它们插入到具有新 的实际表中来自主记录的 SCOPE_IDENTITY()。

经过一番考虑后，我决定完全删除这些表，而将 XML 存储在 XML 字段中。现在我明白这里存在​​一些陷阱，例如一般查询性能，GROUP BY 不适用于 XML 数据。查询通常有点混乱，但总的来说，我喜欢现在在取回数据时可以使用 XElement。

而且，这些东西不会改变。这是一次性的事情，所以我不必担心修改。

我想知道实际获取这些数据的最佳方法。我的很多查询都涉及根据子记录甚至子子记录的标准获取主记录。数据库中的大多数存储过程都会执行此操作，但规模要复杂得多，通常需要 UDF 和子查询才能有效工作，但我已经敲出了一个简单的示例来测试查询某些数据......

INSERT INTO Customers VALUES ('Tom', '', '<PhoneNumbers><PhoneNumber Type="1" Value="01234 456789" /><PhoneNumber Type="2" Value="01746 482954" /></PhoneNumbers>')
INSERT INTO Customers VALUES ('Andy', '', '<PhoneNumbers><PhoneNumber Type="2" Value="07948 598348" /></PhoneNumbers>')
INSERT INTO Customers VALUES ('Mike', '', '<PhoneNumbers><PhoneNumber Type="3" Value="02875 482945" /></PhoneNumbers>')
INSERT INTO Customers VALUES ('Steve', '', '<PhoneNumbers></PhoneNumbers>')

现在我可以看到两种获取它的方法。

方法1

DECLARE @PhoneType INT
SET  @PhoneType = 2

SELECT ct.*
FROM Customers ct
WHERE ct.PhoneNumbers.exist('/PhoneNumbers/PhoneNumber[@Type=sql:variable("@PhoneType")]') = 1

真的吗？ sql:variable 感觉有点不健康。然而，它确实有效。然而，以更有意义的方式访问数据显然更加困难。

方法2

SELECT ct.*, pt.PhoneType
FROM Customers ct
  CROSS APPLY ct.PhoneNumbers.nodes('/PhoneNumbers/PhoneNumber') AS nums(pn)
  INNER JOIN PhoneTypes pt ON pt.ID = nums.pn.value('./@Type[1]', 'int')
WHERE nums.pn.value('./@Type[1]', 'int') = @PhoneType

这个比较像。我已经可以轻松扩展它来执行连接和所有其他好东西。我之前在表值函数上使用过CROSS APPLY，效果非常好。与之前的查询相比，此查询的执行计划更加先进。诚然，我没有对这些表进行任何索引等操作，但它占整个批次成本的 97%。

方法 2（扩展）

SELECT ct.ID, ct.CustomerName, ct.Notes, pt.PhoneType
FROM Customers ct
  CROSS APPLY ct.PhoneNumbers.nodes('/PhoneNumbers/PhoneNumber') AS nums(pn)
  INNER JOIN PhoneTypes pt ON pt.ID = nums.pn.value('./@Type[1]', 'int')
WHERE nums.pn.value('./@Type[1]', 'int') IN (SELECT ID FROM PhoneTypes)

这里很好的 IN 子句。我还可以做类似 pt.PhoneType = 'Work'

最后

所以我基本上获得了我想要的结果，但是在使用这种机制来询问少量内容时我应该注意什么XML 数据？在复杂的搜索过程中它的性能会下降吗？存储这种标记样式的数据是否开销太大？

旁注：

我过去使用过诸如 sp_xml_preparedocument 和 OPENXML 之类的东西，只是将列表传递到存储过程中，但相比之下，这就像呼吸新鲜空气一样！

Answer 1

对于存储在 XML 列中的一些关键信息项，我们采取的一种方法是将它们“显示”为“父”表上的计算的、持久的属性。这是使用一个小的存储函数来完成的。

它工作得很好，因为每次 XML 更改时只计算一次该值 - 只要它没有更改，就不需要重新计算，该值像任何其他列一样存储在表中。

它也很棒，因为它可以被索引！因此，如果您正在寻找和/或加入这样的领域 - 这就像一个魅力！

所以你基本上需要一个类似这样的存储函数：

CREATE FUNCTION [dbo].[GetPhoneNo1](@DataXML XML)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS BEGIN
      DECLARE @result VARCHAR(20)

      SELECT 
        @result = @DataXML.value('(/PhoneNumbers/PhoneNumber[@Type="1"]/@Value)[1]', 'VARCHAR(50)')
      RETURN @result
END

如果你没有类型 1 的电话号码，你只会返回 NULL。

然后，您需要使用计算的持久列来扩展父表：

ALTER TABLE dbo.Customers
   ADD PhoneNumberType1 AS dbo.GetPhoneNo1(PhoneNumbers)

如您所见 - 它对于单个条目效果很好，但不幸的是，您无法显示完整的属性列表。但是，如果您有一些关键项目，例如 ID 等，您希望大多数行都具有这些项目，那么这可能是一种非常好的、灵活的方式，可以更轻松、更有效地获取该信息。