Extracting values from XML field in MS-SQL Server 2008

Question

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.

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.

    <ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
     xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <EntityPropertyOfString>
       <Name>Project Manager</Name>
       <Value>DBD</Value>
     </EntityPropertyOfString>
     <EntityPropertyOfString>
       <Name>Profit Center</Name>
       <Value>211</Value>
     </EntityPropertyOfString>
    </ArrayOfEntityPropertyOfString>

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".

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.

    SELECT md.LName, md.FName, pc.ProfitCenterName
    FROM tblMainDataCenter md
    LEFT OUTER JOIN tblProfitCenter pc ON md.pcID = pc.prftData

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.

Answer 1

You can use the valuefunction on an XML column to extract data from the XML like this:

SELECT col.value('(/ArrayOfEntityPropertyOfString/EntityPropertyOfString[Name="Profit Center"]/Value)[1]', 'int')
FROM tbl

assuming your table is named tbland the XML column is named col.

The first argument is an XPath 1 expression, and the second the target data type. Please note that these strings must be fix strings in SQL Server and cannot be built dynamically e. g. by string concatenation.