title: How to display xml data as tabular format
body: <p>here is my xml which i want to show as tabular form</p>
  
  <pre><code>DECLARE @xml XML 
  SET @xml = '&lt;root&gt;
    &lt;row&gt;one&lt;/row&gt;
    &lt;row&gt;two&lt;/row&gt;
    &lt;row&gt;three&lt;/row&gt;
  &lt;/root&gt;'
  
  SELECT 
      x.y.value('(row/text())[1]', 'varchar(5)')
  FROM @xml.nodes('root') x(y)
  </code></pre>
  
  <p>it is displaying only first rows.
  i want to display data like</p>
  
  <pre><code>Mycolumn
  -----------
  one
  two
  three
  </code></pre>
  
  <p>please guide me what i need to change in my sql. thanks</p>
  
  <p>i could not issue select into with xquery like the below way</p>
  
  <pre><code>DECLARE @xml XML 
  SET @xml = '&lt;root&gt;
    &lt;row&gt;one&lt;/row&gt;
    &lt;row&gt;two&lt;/row&gt;
    &lt;row&gt;three&lt;/row&gt;
  &lt;/root&gt;'
  
  SELECT *
  INTO #Fields
  FROM (SELECT 
      x.y.value('text()[1]', 'varchar(5)') as fields11
  FROM @xml.nodes('root/row') x(y))
  
  SELECT * FROM #Fields
  DROP TABLE #Fields
  </code></pre>
  
  <p>but i could use insert into with xuqery like below way</p>
  
  <pre><code>DECLARE @xml XML 
  SET @xml = '&lt;root&gt;
    &lt;row&gt;one&lt;/row&gt;
    &lt;row&gt;two&lt;/row&gt;
    &lt;row&gt;three&lt;/row&gt;
  &lt;/root&gt;'
  
  CREATE TABLE #Fields(Field varchar(MAX))
  
  INSERT INTO #Fields
  SELECT 
      x.y.value('text()[1]', 'varchar(5)') 
  FROM @xml.nodes('root/row') x(y)
  
  SELECT * FROM #Fields
  DROP TABLE #Fields
  </code></pre>
  
  <p>so anyone can help me to construct select into with xquery. thanks</p>
  
  <h2>NOW Select into works with XQUERY</h2>
  
  <pre><code>DECLARE @xml XML 
  SET @xml = '&lt;root&gt;
    &lt;row&gt;one&lt;/row&gt;
    &lt;row&gt;two&lt;/row&gt;
    &lt;row&gt;three&lt;/row&gt;
  &lt;/root&gt;'
  
  SELECT  x.y.value('text()[1]', 'varchar(5)') Mycolumn into #Fields
  FROM    @xml.nodes('root/row') x(y)
  
  select * from #Fields
  drop table #Fields
  </code></pre>