在 SQLServer 2005 中查询 XML 列

2021-10-02 00:00:00 xml xquery sql-server

我公司的联系人"表中有一个字段.在该表中,有一个 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

相关文章