Querying XML Columns in SQL Server 2005

Question

In my company "Contacts" there is a field. This table has an XML type column. The column contains different information about a specific contact. EG.

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

The tags below contactmay be different for each contact, and I have to query for these fragments along with columns of relational data in the same table.

I used constructs such as:

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 fine, but the server takes time. I also explored using the nodes () function to parse the XML nodes and exist () to check if the node supports the value I'm looking for.

Does anyone know a better way to query XML columns?

Answer 1

msdn xml, blob xml, ... http://msdn.microsoft.com/en-us/library/ms345115.aspx#sql25xmlbp_topic4

Answer 2

, , . , , name/value/contactID.

Answer 3

, @pauljette, :

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

, XML-, , . , , , , -, ( ).

Answer 4

XSD Xml, , Xml.

Answer 5

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