How to display xml data as tabular format

Question

here is my xml which i want to show as tabular form

DECLARE @xml XML 
SET @xml = '<root>
  <row>one</row>
  <row>two</row>
  <row>three</row>
</root>'

SELECT 
    x.y.value('(row/text())[1]', 'varchar(5)')
FROM @xml.nodes('root') x(y)

it is displaying only first rows. i want to display data like

Mycolumn
-----------
one
two
three

please guide me what i need to change in my sql. thanks

i could not issue select into with xquery like the below way

DECLARE @xml XML 
SET @xml = '<root>
  <row>one</row>
  <row>two</row>
  <row>three</row>
</root>'

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

but i could use insert into with xuqery like below way

DECLARE @xml XML 
SET @xml = '<root>
  <row>one</row>
  <row>two</row>
  <row>three</row>
</root>'

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

so anyone can help me to construct select into with xquery. thanks

NOW Select into works with XQUERY

DECLARE @xml XML 
SET @xml = '<root>
  <row>one</row>
  <row>two</row>
  <row>three</row>
</root>'

SELECT  x.y.value('text()[1]', 'varchar(5)') Mycolumn into #Fields
FROM    @xml.nodes('root/row') x(y)

select * from #Fields
drop table #Fields

Ivan Golović · Accepted Answer · 2013-03-05 11:41:34Z

2

This should work:

SELECT  x.y.value('text()[1]', 'varchar(5)') Mycolumn
FROM    @xml.nodes('root/row') x(y)

Path in nodes method from which the rows are generated should be root/row, this way three rows will be generated and for each of the values in rows expression text()[1] should extract it's value.

answered Mar 5, 2013 at 11:41

Ivan Golović

8,8223 gold badges27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Over a year ago

thanks i have updated my question can u plzz have a look. thanks again for answer.

Ivan Golović Over a year ago

You're welcome, I'm glad it helped. For SELECT...INTO...FROM you just have to add table alias. Last line of the failing query should be for instance: FROM @xml.nodes('root/row') x(y)) tbl where tbl is alias.

Collectives™ on Stack Overflow

How to display xml data as tabular format

NOW Select into works with XQUERY

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

NOW Select into works with XQUERY

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related