求一个XML格式数据导出问题

Question

我们原来有一个表A，表的架构如下:ID     VALUE
------------------------------------------------------------
1      <?xml version="1.0" encoding="gb2312"?><root><A>1</A><B>sda</B></root>
2      <?xml version="1.0" encoding="gb2312"?><root><B>3</B></root>
.......
-------------------------------------其中value字段是一个XML格式的字符串，现在想把其中的节点A中数据导入到另外一张表B,导入的结果如下:ID   VALUE_A
--------------------------------------------
1      1
2      0 
.......
(其中如果xml不含A节点则以0作为默认值填充)我查询了sql server相关XML的操作，可是没有什么头绪，各位有什么好的SQL语句来完成这个导入操作么，谢谢了。

Answer 1

网太差了
create table A(id int, value varchar(1000))
insert A
select 1,'<?xml version="1.0" encoding="gb2312"?><root><A>1</A><B>sda</B></root>' union all
select 2,'<?xml version="1.0" encoding="gb2312"?><root><B>3</B></root>'create table B (id int,  VALUE_A varchar(1000))--这样做可否
declare @i int
declare @value varchar(1000)
DECLARE @idoc int
declare cur_a cursor for select * from A
open cur_a
fetch cur_a into @i,@value
while @@fetch_status=0
begin
EXEC sp_xml_preparedocument @idoc OUTPUT, @value
insert B SELECT @i,isnull(A,0)FROM OPENXML (@idoc, '/root',2)WITH (A  varchar(10)) 
EXEC sp_xml_removedocument @iDoc
fetch cur_a into @i,@value
end
select * from B
/*（所影响的行数为 1 行）
（所影响的行数为 1 行）id          VALUE_A                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           1
2           0（所影响的行数为 2 行）
*/