在SQL Server 2005中查询XML列

Question

我的公司的“联系人”表中有一个字段。在该表中，有一个XML类型的列。该列保存有关特定联系人的杂项数据。例如：

<contact>
<refno>123456</refno>
<special>a piece of custom data</special>
</contact>

以下标签contact的内容对于每个联系人可能都不同，我必须查询这些片段并将其与同一表中的关系数据列一起使用。我已经使用了如下的构造:

SELECT c.id AS ContactID,c.ContactName as ForeName,
c.xmlvaluesn.value('(contact/Ref)[1]', 'VARCHAR(40)') as ref,    
INNER JOIN ParticipantContactMap pcm ON c.id=pcm.contactid 
AND pcm.participantid=2140
WHERE xmlvaluesn.exist('/contact[Ref = "118985"]') = 1

这种方法可以正常工作，但是服务器需要一段时间才能响应。我还尝试使用 nodes() 函数来解析 XML 节点，并使用 exist() 来测试节点是否包含我正在搜索的值。

有人知道查询 XML 列的更好方法吗？

- nialljsmith

---

你所说的“耗时”，是指服务器响应时间长，还是查询语句编写需要花费大量精力？ - Espo

服务器返回结果的时间太长了。 - nialljsmith

Answer 1

如果您进行一次写入和大量读取，请在写入时解析数据，并将其转换成更易于查询的格式。首先建议将其解析为相关但单独的表，其中包含名称/值/联系人ID列。

- Tina Marie

Answer 2

我发现MSDN XML最佳实践对于处理XML blob列非常有帮助，可能会提供一些灵感... http://msdn.microsoft.com/en-us/library/ms345115.aspx#sql25xmlbp_topic4

- Paulj

Answer 3

试试这个

SELECT * FROM conversionupdatelog WHERE convert(XML, colName).value('(/leads/lead/@LeadID=''[e-mail]'')[1]', 'varchar(max)')='true'

- Shailesh

Answer 4

除了@pauljette提到的页面外，该页面还有很好的性能优化建议：

http://msdn.microsoft.com/en-us/library/ms345118.aspx

有很多方法可以加速XML查询的性能，但它永远不会像适当索引的关系数据那样好。如果您选择一个文档，然后在其中查询，您可以做得很好，但是当您的查询需要扫描一堆类似的文档以查找某些内容时，它就像关系查询计划中的键查找（也就是慢）。

- Eric Z Beard

---

我在代码重访期间阅读了这篇文章。我遵循了其中的一些建议，但是我的数据库受到了很大的限制，我所能做的事情非常有限。我们的SQL Server只有大约6个月前才升级到2005版。 - nialljsmith

Answer 5

如果您有一个针对Xml的XSD，则可以将其导入到数据库中，然后可以为Xml数据构建索引。

- MotoWilliams