举例如下:
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进行关联操作，@[email protected];
如: @xml2 [email protected] 中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)