在 SQLServer 2005 中查询 XML 列
我公司的联系人"表中有一个字段.在该表中，有一个 XML 类型列.该列包含有关特定联系人的杂项数据.例如.
There is a field in my company's "Contacts" table. In that table, there is an XML type column. The column holds misc data about a particular contact. EG.
<contact>
<refno>123456</refno>
<special>a piece of custom data</special>
</contact>
contact 下面的标签对于每个联系人可以不同，我必须查询这些片段同一个表中的关系数据列.
The tags below contact can be different for each contact, and I must query these fragments
alongside the relational data columns in the same table.
我使用过如下结构:
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() 来测试节点是否包含我正在搜索的值.
This method works ok but, it takes a while for the Server to respond. I have also investigated using the nodes() function to parse the XML nodes and exist() to test if a nodes holds the value I'm searching for.
有人知道查询 XML 列的更好方法吗??
Does anyone know a better way to query XML columns??
推荐答案
我发现 msdn xml 最佳实践有助于处理 xml blob 列，可能会提供一些灵感...http://msdn.microsoft.com/en-us/library/ms345115.aspx#sql25xmlbp_topic4
I've found the msdn xml best practices helpful for working with xml blob columns, might provide some inspiration... http://msdn.microsoft.com/en-us/library/ms345115.aspx#sql25xmlbp_topic4
相关文章