sql - retrieving xml node from multiple raws -

i retreieved column using inner join on 2 tables:

select table_1.*, table_2.c table_1 inner join table_2 on charindex(table_2.a, table_1.b) > 0 , table_2.c=1 table_1.d '%xxx...%'

one of column in above query type xml:

<column1> --------- <xml1> --------- <xml2> --------- <xml3> --------- . . .

1.i want retrieve string each xml , display additional column in query (all xml structure identical)? thanks. lets xml follow:

<root>  <row>   <string>xxx...</string>  </row> </root>

okay, assume column of table contain xml of below format.

<root>   <row>     <value>1</value>   </row> </root>

then check below query.

select  b.id,        x.xmlcol.value('value[1]', 'int') [nodeval]    yourtable b cross apply b.actualxmlcol.nodes('/root/row') x(xmlcol);

And