Import XML into SQL Server database

Question

I have a problem with putting an XML file to a database.

My XML file looks like this:

<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>

Each tag includes 10 tags with data that should be put into columns. That tag is the same, only thing that differs them is name attribute(it's the name of column - that tag).

I try to put it into an SQL table with columns named: WOJ, POW, GMI, RODZ_GMI, RM, MZ, NAZWA, SYM, SYMPOD, STAN_NA.

Here is my attempt with 2 cursors. But it is blocked on first row somehow - it's my prediction what is wrong.

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

This XML is not valid - the sequence of the opening and closing tags doesn't match.... — marc_s
– marc_s, Commented Dec 1, 2014 at 15:29

marc_s · Accepted Answer · 2014-12-01 15:35:49Z

2

Assuming you have the valid XML in a SQL Server variable @Input, you can use something like this:

INSERT INTO dbo.YourTableName(WOJ, POW, GMI, RODZ_GMI, RM, MZ, NAZWA, SYM, SYMPOD, STAN_NA)
    SELECT
       WOJ = xc.value('(col[@name="WOJ"])[1]', 'varchar(50)'),
       POW = xc.value('(col[@name="POW"])[1]', 'varchar(50)'),
       GMI = xc.value('(col[@name="GMI"])[1]', 'varchar(50)'),
       RODZ_GMI = xc.value('(col[@name="RODZ_GMI"])[1]', 'varchar(50)'),
       RM =xc.value('(col[@name="RM"])[1]', 'varchar(50)'),
       MZ =xc.value('(col[@name="MZ"])[1]', 'varchar(50)'),
       NAZWA =xc.value('(col[@name="NAZWA"])[1]', 'varchar(50)'),
       SYM =xc.value('(col[@name="SYM"])[1]', 'varchar(50)'),
       SYMPOD =xc.value('(col[@name="SYMPOD"])[1]', 'varchar(50)'),
       TAN_NA =xc.value('(col[@name="STAN_NA"])[1]', 'varchar(50)')
    FROM
       @input.nodes('/teryt/catalog/row') AS XT(XC)

answered Dec 1, 2014 at 15:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Whencesoever Over a year ago

it actually works. But would it work for 30 mb xml file? For big data?

Whencesoever Over a year ago

For 800 K lines XML it is taking ages, can u think of anything for big data? Here 1 record is put into table each 3 seconds

Whencesoever · Accepted Answer · 2014-12-05 11:05:31Z

I managed to do this for big data also. Here is solution for large xml files- Your worked for small ones.

CREATE TABLE [dbo].[SIMC](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [WOJ] [int] NULL,
    [POW] [int] NULL,
    [GMI] [int] NULL,
    [RODZ_GMI] [int] NULL,
    [RM] [varchar](max) NULL,
    [MZ] [int] NULL,
    [NAZWA] [varchar](max) NULL,
    [SYM] [varchar](max) NULL,
    [SYMPOD] [varchar](max) NULL,
    [STAN_NA] [date] NULL
) ON [PRIMARY]

GO

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_BLOB )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 kursorMain CURSOR LOCAL FORWARD_ONLY FOR
SELECT *
FROM OPENXML (@DocID, 'teryt/catalog/row',2)
WITH
(
    WOJ  varchar(max) 'col[@name="WOJ"]',
    POW varchar(max) 'col[@name="POW"]',
    GMI varchar(max) 'col[@name="GMI"]',
    RODZ_GMI varchar(max) 'col[@name="RODZ_GMI"]',
    RM varchar(max) 'col[@name="RM"]',
    MZ varchar(max) 'col[@name="MZ"]',
    NAZWA varchar(max) 'col[@name="NAZWA"]',
    SYM varchar(max) 'col[@name="SYM"]',
    SYMPOD varchar(max) 'col[@name="SYMPOD"]',
    STAN_NA varchar(max) 'col[@name="STAN_NA"]'   
)
Open KursorMain
fetch next from KursorMain into @WOJ , @POW , @GMI , 
@RODZ_GMI , @RM , @MZ ,
@NAZWA , @SYM   ,@SYMPOD ,
@STAN_NA

 WHILE @@FETCH_STATUS = 0

  BEGIN

INSERT INTO SIMC(WOJ, POW, GMI, RODZ_GMI, RM, MZ, NAZWA, SYM, SYMPOD, STAN_NA)
values (@WOJ , @POW , @GMI , 
@RODZ_GMI , @RM , @MZ ,
@NAZWA , @SYM   ,@SYMPOD ,
@STAN_NA)

fetch next from KursorMain into @WOJ , @POW , @GMI , 
@RODZ_GMI , @RM , @MZ ,
@NAZWA , @SYM   ,@SYMPOD ,
@STAN_NA


END
EXEC sp_xml_removedocument @DocID
  CLOSE KursorMain
  DEALLOCATE KursorMain

Collectives™ on Stack Overflow

Import XML into SQL Server database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related