将XML导入SQL Server数据库

将XML文件放入数据库时​​出现问题。

我的XML文件如下所示：

<teryt>
<catalog name="SIMC" type="all" date="2014-01-01">
<row>
<col name="WOJ">12</col>
<col name="POW">10</col>
<col name="GMI">16</col>
<col name="RODZ_GMI">5</col>
<col name="RM">00</col>
<col name="MZ">1</col>
<col name="NAZWA">Zagórze</col>
<col name="SYM">1047995</col>
<col name="SYMPOD">0465897</col>
<col name="STAN_NA">2014-01-01</col>
</row>
<row>
<col name="WOJ">14</col>
<col name="POW">36</col>
<col name="GMI">02</col>
<col name="RODZ_GMI">2</col>
<col name="RM">00</col>
<col name="MZ">1</col>
<col name="NAZWA">Wyspa</col>
<col name="SYM">1058763</col>
<col name="SYMPOD">0631864</col>
<col name="STAN_NA">2014-01-01</col>
</row>
</catalog>

每个标签包含10个标签，这些数据应放入列中。该标签是相同的，唯一不同的是名称属性（它是列的名称-该标签）。

我尝试将其放入具有以下名称的列的SQL表中：WOJ，POW，GMI，RODZ_GMI，RM，MZ，NAZWA，SYM，SYMPOD，STAN_NA。

这是我尝试使用2个游标。但是它以某种方式在第一行被阻止-这是我的预言，这是错误的。

DECLARE @DocID INT;
DECLARE @DocXML XML;
DECLARE @sqlstmt nVARCHAR(MAX);
DECLARE @results table (result xml)

--Build the Dynamic SQL Statement to get the data from the xml file
SET @sqlstmt= 'SELECT * FROM OPENROWSET ( BULK ''C:\SIMC.xml'', SINGLE_CLOB )AS xmlData'

-- Insert the results of the dynamic SQL Statement into the temporary table variable.
INSERT INTO @results EXEC (@sqlstmt)

--DECLARE @xmlDoc XML 
SELECT @DocXML = result FROM @results 
EXECUTE sp_xml_preparedocument @DocID OUTPUT, @DocXML;


Declare @WOJ integer, @POW integer, @GMI integer, 
@RODZ_GMI integer, @RM varchar(max), @MZ integer,
@NAZWA varchar(max), @SYM   varchar(max),@SYMPOD varchar(max),
@STAN_NA date
Declare @zmiennanacolumn xml
declare @rowid int
declare @dane varchar


set @rowid=1;
DECLARE kursorMain CURSOR LOCAL FORWARD_ONLY FOR

SELECT 
*
from OPENXML(@DocID, 'teryt/catalog/row', 2)

Open KursorMain
fetch next from KursorMain into @rowid,@zmiennanacolumn

 WHILE @@FETCH_STATUS = 0

  BEGIN

  --tutaj robić coś z tymi danymi które mam
  DECLARE @h2 int
  EXEC sp_xml_preparedocument @h2 output, @zmiennanacolumn

  DECLARE kursorMainInside CURSOR LOCAL FORWARD_ONLY FOR

  select [col]
from OPENXML(@h2,'',2) with [col] varchar

Open kursorMainInside
  declare @columnnumber int
  set @columnnumber=1

FETCH NEXT FROM KursorMainInside into @dane

 WHILE @@FETCH_STATUS = 0

  BEGIN
if(@columnnumber=1) set @WOJ = @dane
else if(@columnnumber=2) set @POW = @dane
else if(@columnnumber=3) set @GMI = @dane
else if(@columnnumber=4) set @RODZ_GMI = @dane
else if(@columnnumber=5) set @RM = @dane
else if(@columnnumber=6) set @MZ = @dane
else if(@columnnumber=7) set @NAZWA = @dane
else if(@columnnumber=8) set @SYM = @dane
else if(@columnnumber=9) set @SYMPOD = @dane
else set @STAN_NA = @dane
--tutaj patrzeć które przejście i dodawać do inta

set @columnnumber = @columnnumber +1

FETCH NEXT FROM KursorMainInside into @dane


Insert into [dbo.SIMC] values (@rowid, @WOJ , @POW , @GMI , 
@RODZ_GMI , @RM , @MZ ,
@NAZWA , @SYM   ,@SYMPOD ,
@STAN_NA)

END
EXEC sp_xml_removedocument @h2
  CLOSE KursorMainInside
  DEALLOCATE KursorMainInside

 set @rowid=@rowid+1

FETCH NEXT FROM KursorMain  into @rowid,@zmiennanacolumn

END
EXEC sp_xml_removedocument @DocID
  CLOSE KursorMain
  DEALLOCATE KursorMain