sql 两个xml关联操作解决思路

sql 两个xml关联操作
举例如下:
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>'

如何对这两个xml进行关联操作，@xml2中的Col_DBName节点的值要等于@xml1中的节点名称;

如: @xml2 中Col_DBName节点的值==@xml 中book的节点名称
结果为:
<book>kl</book>
<leng>33</leng>

求解各位达人~~~~~~~~~


[解决办法]

SQL code

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)') tfrom @xml1.nodes('/root/*') as t(c)) as ainner 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 bon a.t=b.Col_DBNamewx-----------------------------<book default="1">kl</book><leng default="1">33</leng>(2 row(s) affected)