Sql-server – Import XML files into SQL Server 2012

importsql serversql-server-2012xml

Every Friday I have to import a couple (sometimes more than 300) of XML files into 2 tables.

The structure of one of the tables, R000000, looks like this:

R00000010 | R00000020 | R00000030 | R00000040 | R00000050 | R00000060 
---------- ------------ ---------- ----------- ----------- ----------
R000000   |     I     |   0002    |     1     |     2     |    0026
R000000   |     I     |   0003    |     1     |     2     |    0025
R000000   |     I     |   0004    |     1     |     2     |    0021
R000000   |     I     |   0006    |     1     |     2     |    0023
R000000   |     I     |   0001    |     1     |     2     |    0022

^
Each row corresponds to an XML file.

The structure doesn't change, only the data (in this case I've placed some random ones for e.g).

The XML files look like this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<ns0:P4131 xmlns:ns0="http://switching/xi">
<R000000>
    <R00000010>R000000</R00000010>
    <R00000020>I</R00000020>
    <R00000030>0002</R00000030>
    <R00000040>1</R00000040>
    <R00000050>0026</R00000050>
    <R00000060>2</R00000060>
</R000000>
</ns0:P4131>

What is the best way to do this? I'm currently doing this in Access.

Best Answer

Give something like the below a try...

You'll obviously need to plug in your variables for your environment, check the data types (may need to add logic to keep leading zeros?), change from the final temp tables to your regular table(s), etc.

Works fine for me for import from XML files to temp tables without deleting the files afterwards but adding logic to delete files from the UNC path shouldn't be too difficult with another xp_cmdshell command.

DECLARE @folder AS VARCHAR(1000) = '\\servername\sharename\folder\subfolder1\'
DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)

-- create global temp table
IF OBJECT_ID('tempdb..##XMLImport') IS NOT NULL
    DROP TABLE ##XMLImport

CREATE TABLE ##XMLImport (
    R00000010 VARCHAR(7)
    ,R00000020 VARCHAR(1)
    ,R00000030 INT
    ,R00000040 INT
    ,R00000050 INT
    ,R00000060 INT
    )

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command

-- create cursor
DECLARE filecurs CURSOR
FOR
SELECT REPLACE(filenameswithfolder, @folder, '') AS filenames
FROM @filesinafolder
WHERE filenameswithfolder IS NOT NULL

OPEN filecurs

FETCH NEXT
FROM filecurs
INTO @file

IF @file = 'FILE NOT FOUND'
    GOTO exitprocessing

WHILE @@fetch_status != - 1
BEGIN
    SET @sql = 'DECLARE @X XML

                    SELECT @X = P
                    FROM OPENROWSET(BULK ''' + @folder + '' + @file + ''', SINGLE_BLOB) AS Products(P)

                    DECLARE @iX INT

                    EXEC sp_xml_preparedocument @iX OUTPUT
                        ,@X

                    SELECT *
                    INTO #XMLResults
                    FROM OPENXML(@iX, ''/*/*'', 2) WITH (
                            R00000010 VARCHAR(7)
                            ,R00000020 VARCHAR(1)
                            ,R00000030 INT
                            ,R00000040 INT
                            ,R00000050 INT
                            ,R00000060 INT
                            )
                    EXEC sp_xml_removedocument @iX

                    INSERT INTO ##XMLImport
                    SELECT R00000010
                        ,R00000020
                        ,R00000030
                        ,R00000040
                        ,R00000050
                        ,R00000060
                    FROM #XMLResults'

    --PRINT @sql

    EXEC sp_executesql @sql

    -- process next file
    FETCH NEXT
    FROM filecurs
    INTO @file
END

exitprocessing:

-- clean up
CLOSE filecurs

DEALLOCATE filecurs

SELECT *
FROM ##XMLImport
Related Question