我在SQL Server中有代码,该代码从XML提取标记并将它们存储在临时表(#result)中。现在,假设所有XML的结构都相同,则游标针对每个XML循环运行。下面的示例:
XML1 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>
结果表:
Tag1 | Tag2 | Tag3
--------|-----------|--------
val1 | val2 | val3
但是现在我们有了一些XML,这些XML可以包含更少的标签。下面的示例:
XML1 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>
XML2 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3><Tag4>val4</Tag4></Root>
XML3 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2></Root>
结果表:
Tag1 | Tag2 | Tag3 | Tag4
--------|-----------|-----------|--------
val1 | val2 | val3 |
--------|-----------|-----------|--------
val1 | val2 | val3 | val4
--------|-----------|-----------|--------
val1 | val2 | |
下面是我现有的光标代码。
Open C_XML
Fetch next from C_XML into @input_xml
while @@FETCH_STATUS = 0
Begin
Create Table #MyTempTable (
name varchar(max),value varchar(max)
);
insert into #MyTempTable
SELECT
bar.value('local-name(.)','VARCHAR(max)') as name,bar.value('./.','VARCHAR(max)') as value
FROM
@input_xml.nodes('/Root/*') AS input_xml(bar)
DeclARE @name NVARCHAR(MAX) = ''
DeclARE @val NVARCHAR(MAX) = ''
Declare @Query NVARCHAR(MAX) = ''
SELECT @name += QUOTENAME(name)+ ','
FROM
(
SELECT name
FROM #MyTempTable
) AS ColName
-- remvoing last comma
SET @name = LEFT(@name,LEN(@name)-1)
SET @Query =
'SELECT * INTO ##temp2 FROM
(SELECT
name,value
FROM
#MyTempTable
)
AS TempTable
PIVOT(
max(value)
FOR name IN (' + @name +')
) AS SPivotTable'
EXECUTE sp_executesql @Query
drop table #MyTempTable
select * from ##temp2
if (@count = 1 )
begin
select * into #result from ##temp2
end
else
begin
insert into #result
select * from ##temp2
end
drop table ##temp2
Set @count = @count + 1
Fetch next from C_XML into @input_xml
END
CLOSE C_XML;
DEALLOCATE C_XML;
请建议我如何增强此代码,以使其对于所有类型的XML都是动态的,如上面的示例所述。当前它给出的错误:
#result
表定义不正确。