Querying XML columns in SQLServer 2005

Question

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>

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.

I have used constructions like:

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

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.

Does anyone know a better way to query XML columns??

Answer 1

If you are doing one write and a lot of reads, take the parsing hit at write time, and get that data into some format that is more query-able. A first suggestion would be to parse them into a related but separate table, with name/value/contactID columns.

Answer 2

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