Databases
StackOverflow2013
Postsdbo
POExtracting values from XML field in MS-SQL Server 2008
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExtracting values from XML field in MS-SQL Server 2008
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USNosharu
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POExtracting values from XML field in MS-SQL Server 2008
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POExtracting values from XML field in MS-SQL Server 2008
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POExtracting values from XML field in MS-SQL Server 2008
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload