title: select all from XML in sql
body: <p>How do I select all nodes from an XML in sql?</p>
  
  <p>XML:</p>
  
  <pre><code>&lt;value&gt;
      &lt;TradeId&gt;5555&lt;/TradeId&gt;
      &lt;Account&gt;SomeAccount&lt;/Account&gt;
      &lt;Book&gt;1&lt;/Book&gt;
      &lt;LocalCcy&gt;XXXX&lt;/LocalCcy&gt;
      &lt;ValuationCcy&gt;XXXX&lt;/ValuationCcy&gt;
      &lt;PVLocal&gt;44444444&lt;/PVLocal&gt;
      &lt;PresentValue&gt;44444444&lt;/PresentValue&gt;
      &lt;InstrumentCode&gt;XXXXXXXXX&lt;/InstrumentCode&gt;
  &lt;/value&gt;
  </code></pre>
  
  <p>My SQL (that doesnt work):</p>
  
  <pre><code>SELECT   tradeRef.value('.','varchar(50)') AS strValue
      FROM @xmlData.nodes('data/value') AS trades(tradeRef)
      CROSS APPLY tradeRef.nodes('.') AS legColumns(nameRef)
  </code></pre>
  
  <p>This gives me all values but in one row...I would like one row per value..</p>
  
  <p>Can you help me?</p>
  
  <p>EDIT:</p>
  
  <p>Requested output (just two first nodes here...):
  <img src="https://i.stack.imgur.com/TsT2B.png" alt="enter image description here"></p>