Query on XML datatype in SQL Server 2008

hi all,

in table have column xml datatype. can able read below xml tag.

set

 

@title = cast(@xmlcontent.query('data(root/chemicalname)') as varchar(50))

<root><chemicalname>sulphuric acid</chemicalname></root>

but how can read (in sql server 2008) with multiple rows in it, , how know list of child nodes , data of dynamically?

<root>

<row>

<

currencycode>gbp</currencycode>

<

currencyname>pound (sterling)</currencyname>

</

row>

<row>

<

currencycode>inr</currencycode>

<

currencyname>rupee</currencyname>

</

row></

try demo:

  create table #tmp ( id int identity primary key, yourxml xml )  go    insert into #tmp values('<root>   <row>    <currencycode>gbp</currencycode>    <currencyname>pound (sterling)</currencyname>   </row>   <row>    <currencycode>inr</currencycode>    <currencyname>rupee</currencyname>   </row>  </root>')  go    -- named elements  select   	t.*,  	x.y.query('.'),  	x.y.value('currencycode[1]', 'varchar(3)') as currencycode,  	x.y.value('currencyname[1]', 'varchar(20)') as currencyname  from #tmp t  	cross apply t.yourxml.nodes('root/row') x(y)  go    -- read elements  select   	x.y.value('local-name(.)', 'varchar(50)'),    x.y.value('.', 'varchar(50)')  from #tmp t  	cross apply t.yourxml.nodes('//*') x(y)  go    -- cleanup  drop table #tmp  go  

SQL Server  >  SQL Server XML