title: How to extract a list of values from a SQL XML column
body: <p>Here is my XML:</p>
  <pre><code>&lt;Triggers&gt;
    &lt;Trigger&gt;
      &lt;Name&gt;DrugName&lt;/Name&gt;
      &lt;Values&gt;
        &lt;Value&gt;Meclofenamate&lt;/Value&gt;
        &lt;Value&gt;Meloxicam&lt;/Value&gt;
        &lt;Value&gt;Vimovo&lt;/Value&gt;
        &lt;Value&gt;Nabumetone&lt;/Value&gt;
        &lt;Value&gt;Qmiiz&lt;/Value&gt;
        &lt;Value&gt;Tolmetin&lt;/Value&gt;    
      &lt;/Values&gt;
    &lt;/Trigger&gt;
    &lt;Trigger&gt;
      &lt;Name&gt;State&lt;/Name&gt;
      &lt;Values&gt;
        &lt;Value&gt;MI&lt;/Value&gt;
      &lt;/Values&gt;
    &lt;/Trigger&gt;
    &lt;Trigger&gt;
      &lt;Name&gt;BenefitType&lt;/Name&gt;
      &lt;Values&gt;
        &lt;Value&gt;Pharmacy&lt;/Value&gt;
      &lt;/Values&gt;
    &lt;/Trigger&gt;
    &lt;Trigger&gt;
      &lt;Name&gt;LineOfBusiness&lt;/Name&gt;
      &lt;Values&gt;
        &lt;Value&gt;Medicaid&lt;/Value&gt;
      &lt;/Values&gt;
    &lt;/Trigger&gt;
  &lt;/Triggers&gt;
  </code></pre>
  <p>My goal is to get output that looks like this:</p>
  <pre><code>ID      DrugName        State     BenefitType   LineOfBusiness
  6500    Meclofenamate   MI        Pharmacy      Medicaid
  6501    Meloxicam       MI        Pharmacy      Medicaid
  6502    Vimovo          MI        Pharmacy      Medicaid
  6503    Nabumetone      MI        Pharmacy      Medicaid
  6504    Qmiiz           MI        Pharmacy      Medicaid
  6505    Tolmetin        MI        Pharmacy      Medicaid
  </code></pre>
  <p>I can't find any examples on stackoverflow after extensive searches where XML is organized this way, and the examples I have found, tweaked and applied result in my getting a list of all the Values in one column (State values, BenefitType values, etc. mixed in with DrugName values).</p>
  <p>The ID column is not part of the XML, but I need to have that in my output.</p>
  <p><a href="https://i.stack.imgur.com/eW7sA.png" rel="nofollow noreferrer">Here what the table looks like that has the XML column. </a></p>