title: Extracting values from XML field in MS-SQL Server 2008
body: <p>I am trying to extract four pieces of information from a MS-SQL Server 2008 database which is stored in a single XML field.  This is the first time I have ever had to work with XML so I am having a bit of trouble and which is why I only have the data I am trying to extract.  I have tried using other postings to solve my problem, but obviously with no luck.</p>
  
  <p>The four pieces of information is first the "Project Manager" and then the "Value" and then 2nd is the "Profit Center" and then that value.  The value from the "Profit Center" is going to be used to do a join between two tables.  Below is a sample of the XML data that is stored in this field.</p>
  
  <pre><code>    &lt;ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
       xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
       &lt;EntityPropertyOfString&gt;
         &lt;Name&gt;Project Manager&lt;/Name&gt;
         &lt;Value&gt;DBD&lt;/Value&gt;
       &lt;/EntityPropertyOfString&gt;
       &lt;EntityPropertyOfString&gt;
         &lt;Name&gt;Profit Center&lt;/Name&gt;
         &lt;Value&gt;211&lt;/Value&gt;
       &lt;/EntityPropertyOfString&gt;
      &lt;/ArrayOfEntityPropertyOfString&gt;
  </code></pre>
  
  <p>So in this example I need to use the "Profit Center" value "211" to join the two tables all in a MS-SQL query.  The table that this information can be called "tblProfitCenter" and the field holding it "prftData".</p>
  
  <p>Here is a made up query that would do the same job if the data in "prftData" was not in XML, but instead was a regular integer field holding the profit center id and performing the join.</p>
  
  <pre><code>    SELECT md.LName, md.FName, pc.ProfitCenterName
      FROM tblMainDataCenter md
      LEFT OUTER JOIN tblProfitCenter pc ON md.pcID = pc.prftData
  </code></pre>
  
  <p>This is for a project at where I work and need to be able move beyond this.  Normally I would be learning XML to solve this, but time will not allow this.  Until I do get a chance to learn XML, I would appreciate any help.</p>