declare @xml1 xml,@xml2 xml
 set @xml1='<root>
 <item>aa</item>
 <book>kl</book>
 <user>rang</user>
 <table>tab</table>
 <pro>asdf</pro>
 <leng>33</leng>
 <asign>52134</asign>
  </root>'

set @xml2='<root>
 <row>
  <Default>1</Default>
  <Col_DBName>book</Col_DBName>
 </row>
  <row>
  <Default>1</Default>
  <Col_DBName>leng</Col_DBName>
 </row>
 </root>'

 
select cast(stuff(cast(a.v as varchar(2000)),
             charindex('>',cast(a.v as varchar(2000))),
             0,
             ' default="'+b.d+'"') as xml) wx 
from
(select t.c.query('.') v,
        t.c.value('local-name(.)','varchar(50)') t
from @xml1.nodes('/root/*') as t(c)) as a
inner join
(select t2.c.value('Default[1]', 'varchar(10)') d,
        t2.c.value('Col_DBName[1]', 'varchar(10)') 'Col_DBName'
from @xml2.nodes('/root/row') t2(c)) as b
on a.t=b.Col_DBName


wx
-----------------------------
<book default="1">kl</book>
<leng default="1">33</leng>

(2 row(s) affected)