xml类型转成@table可以吗？
declare @mydoc xml
set @mydoc= '
<perporty value= "name ">
<city ID= "1 " value= "嘎嘎 "/>
<city ID= "2 " value= "呵呵 "/>
</perporty> '
select @mydoc
希望把@mydoc转成@table结构的
比如
declare @table table
(
id int identity(1,1) not null,
name nvarchar(30) not null
)
insert into @table select ..........from @mydoc
[解决办法]
declare @mydoc xml
set @mydoc= '
<perporty value= "name ">
<city ID= "1 " value= "嘎嘎 "/>
<city ID= "2 " value= "呵呵 "/>
</perporty> '
select @mydoc
declare @table table
(
id int identity(1,1) not null,
name nvarchar(30) not null
)
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @mydoc
insert into @table (name)
SELECT value
FROM OPENXML (@idoc, '/perporty/city ',1)
WITH (ID varchar(10),
value nvarchar(20))
select * from @table
