title: Take out duplicates from XML
body: <p>I need some help with my query...I wan't to get the tradeId:s that are duplicates and are missing a LegId. Can you please help me?</p>
  
  <p>My XML:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" standalone="yes"?&gt;
  &lt;data&gt;
  &lt;value&gt;
      &lt;TradeId&gt;928&lt;/TradeId&gt;
      &lt;LegId&gt;1&lt;/LegId&gt;
  &lt;/value&gt;
  &lt;value&gt;
      &lt;TradeId&gt;928&lt;/TradeId&gt;
      &lt;LegId&gt;2&lt;/LegId&gt;
  &lt;/value&gt;
  &lt;value&gt;
      &lt;TradeId&gt;928&lt;/TradeId&gt;
      //MISSING LEGID HERE
  &lt;/value&gt;
  &lt;value&gt;
      &lt;TradeId&gt;929&lt;/TradeId&gt;
      &lt;LegId&gt;1&lt;/LegId&gt;
  &lt;/value&gt;
  &lt;value&gt;
      &lt;TradeId&gt;929&lt;/TradeId&gt;
      &lt;LegId&gt;2&lt;/LegId&gt;
  &lt;/value&gt;
  &lt;value&gt;
      &lt;TradeId&gt;930&lt;/TradeId&gt;
      &lt;LegId&gt;2&lt;/LegId&gt;
  &lt;/value&gt;
  &lt;/data&gt;
  </code></pre>
  
  <p>Im declaring this XML to a variable and then filling a #temptable with the result:</p>
  
  <pre><code>SELECT *
  INTO #tradeIdDuplicatesToIgnore
  FROM
  (
      SELECT 
           e.value('TradeId[1]','varchar(50)') AS strTradeId
          ,e.value('LegId[1]','int') AS LegId
      FROM @xmlData.nodes('data/value') AS elements(e)
      WHERE   1 = 1
  ) AS t
  
  
  
  SELECT   *
  FROM    #tradeIdDuplicatesToIgnore AS t
  </code></pre>
  
  <p>This gives me following output:
  <img src="https://i.stack.imgur.com/KuD16.png" alt="output"></p>
  
  <p>The only row I wan't in this case is number 3, the one marked in yellow (I only need the TradeId column). This query:</p>
  
  <pre><code>SELECT t.strTradeId
  INTO #tradeIdDuplicatesToIgnore
  FROM
  (
      SELECT 
           e.value('TradeId[1]','varchar(50)') AS strTradeId
          ,e.value('LegId[1]','int') AS LegId
      FROM @xmlData.nodes('data/value') AS elements(e)
  ) AS t
  WHERE   1 = 1
  --AND       t.LegId IS NULL
  GROUP BY  t.strTradeId
  HAVING COUNT(t.strTradeId) &gt; 1
  
  
  SELECT   *
  FROM    #tradeIdDuplicatesToIgnore AS t
  </code></pre>
  
  <p>And that leaves me with two rows with 928 and 929, but I can't get the one were LegId IS NULL...</p>
  
  <p>Requested output from this case: one with TradeId 928.</p>
  
  <p>Can you please help me with this?</p>