Working with Xquery using SQLServer

declare @xml xml;

set @xml='

1234

fxhc

tt

123333

reddy

tt

'

---modify uses 'replace value of','insert','delete'

--select

--t.c.value('@id[1]','int'),

--t.c.value('@type[1]','varchar(300)'),

--t.c.value('count[1]','varchar(300)'),

--m.c.value('red[1]','varchar(300)')

--from

--@xml.nodes('//root/station') t(c)

--cross apply @xml.nodes('//root/station/malli') m(c)

select

t.col.value('@id[1]','int'),

t.col.value('@type[1]','varchar(300)'),

t.col.value('count[1]','varchar(300)'),

m.c.value('(.//red/text())[1]','varchar(300)')

from

@xml.nodes('root/station') t(col)

cross apply t.col.nodes('./malli') m(c)