Importazione dati da file XML a tabella SQL Server
La stored procedure USP_IMPORT_XML_INTO_SQL() definita di seguito permette l’importazione di dati da file XML a tabella SQL Server.
La sorgente dati è rappresentata da un file XML strutturato come il file di esempio TestTab.xml illustrato in figura 1.
Figura 1
La destinazione su cui importare i dati è rappresentata da una tabella SQL Server il cui nome deve essere specificato nel file XML dal tag DATA IDENTIFIER. Nell’esempio, la tabella di destinazione è TESTTAB <DATA IDENTIFIER="TESTTAB">.
-- Drop procedure
IF OBJECT_ID(‘USP_IMPORT_XML_INTO_SQL’, ‘P’) IS NOT NULL
DROP PROCEDURE dbo.USP_IMPORT_XML_INTO_SQL
GO
-- Create procedure
CREATE PROCEDURE dbo.USP_IMPORT_XML_INTO_SQL
(@P_STR_PATH VARCHAR(512),
@P_STR_FILENAME VARCHAR(56),
@P_STR_TABLENAME VARCHAR(32))
AS BEGIN
/*
Descrizione: Importazione da file XML a tabella SQL Server
Parametri: @P_STR_PATH = File path
@P_STR_FILENAME = File name
@P_STR_TABLENAME = Table name
*/
SET NOCOUNT ON
DECLARE @strCommand VARCHAR(1024)
IF(RIGHT(@P_STR_PATH, 1) <> ‘\’)
SET @P_STR_PATH = @P_STR_PATH + ‘\’
/*
Lettura file XML ed importazione dei dati all’interno di una
tabella temporanea
*/
/* Creazione tabella temporanea */
CREATE TABLE #tmpFileLines
(rowID INTEGER IDENTITY,
LineData VARCHAR(255))
/* Insert linea from files into #tmpFileLines (utilizzo di xp_cmdshell) */
SET @strCommand = ‘TYPE ’ + @P_STR_PATH + @P_STR_FILENAME
INSERT #tmpFileLines
EXEC master.dbo.xp_cmdshell @strCommand
DECLARE @strXMLText VARCHAR(8000),
@strCur_Line_XML VARCHAR(8000),
@strCur_Line_XML_Reverse VARCHAR(8000),
@str_Line_XML_To_Insert VARCHAR(8000),
@str_Line_XML_Res VARCHAR(8000),
@i INTEGER,
@j INTEGER,
@z INTEGER,
@hDoc INTEGER,
@str_TMP VARCHAR(512)
/* Lettura dei dati XML dalla tabella temporanea */
DECLARE Cur_Read_Line_XML CURSOR
FOR
SELECT ISNULL(RTRIM(LineData), ‘’)
FROM #tmpFileLines
ORDER BY rowID ASC
OPEN Cur_Read_Line_XML
FETCH NEXT FROM Cur_Read_Line_XML INTO @strCur_Line_XML
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @i = CHARINDEX(‘DATA IDENTIFIER="’ + @P_STR_TABLENAME + ‘"’,
@strCur_Line_XML)
IF (@i > 0)
BEGIN
SET @str_TMP = ‘DATA IDENTIFIER="’ + @P_STR_TABLENAME + ‘"’
SELECT @strCur_Line_XML = REPLACE(@strCur_Line_XML,
@str_TMP,
‘DATA’)
END
SET @strCur_Line_XML_Reverse = REVERSE(@strCur_Line_XML)
SET @j = CHARINDEX(’>DROCER/<’, @strCur_Line_XML_Reverse)
IF (@j = 0)
BEGIN
SET @str_Line_XML_Res = @strCur_Line_XML
FETCH NEXT FROM Cur_Read_Line_XML INTO @strCur_Line_XML
SET @strCur_Line_XML = @str_Line_XML_Res + @strCur_Line_XML
CONTINUE
END
--PRINT LEN(@strCur_Line_XML)
SET @z = (LEN(@strCur_Line_XML) - @j + 1)
SET @str_Line_XML_To_Insert = SUBSTRING(@strCur_Line_XML, 1, @z)
SET @str_Line_XML_Res = SUBSTRING(@strCur_Line_XML, @z+1, LEN(@strCur_Line_XML))
/* Preparazione documento per utilizzo OPENXML */
IF (CHARINDEX(’’, UPPER(@str_Line_XML_To_Insert)) > 0)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @str_Line_XML_To_Insert
ELSE BEGIN
SET @str_Line_XML_To_Insert = @str_Line_XML_To_Insert + ‘’
EXEC sp_xml_preparedocument @hDoc OUTPUT, @str_Line_XML_To_Insert
END
-- Inserimento utilizzando OPENXML
--PRINT (@str_Line_XML_To_Insert)
-- Import TESTTAB
IF (UPPER(@P_STR_TABLENAME)=UPPER(‘TESTTAB’))
INSERT INTO TESTTAB
SELECT *
FROM OPENXML(@hDOC, ‘/DATA/RECORD’, 2)
WITH TESTTAB
-- Import Tabella1 (esempio)
ELSE IF (UPPER(@P_STR_TABLENAME)=UPPER(‘Tabella1’))
INSERT INTO Tabella1
SELECT *
FROM OPENXML(@hDOC, ‘/DATA/RECORD’, 2)
WITH Tabella1
-- ELSE… Import Tabella2 (esempio)
-- …
-- …
ELSE
PRINT ‘L’‘Import della tabella ’ + @P_STR_TABLENAME + ’ non è gestito.’
EXEC sp_xml_removedocument @hdoc
FETCH NEXT FROM Cur_Read_Line_XML INTO @strCur_Line_XML
SET @strCur_Line_XML = ‘’ + @str_Line_XML_Res + @strCur_Line_XML
END
CLOSE Cur_Read_Line_XML
DEALLOCATE Cur_Read_Line_XML
DROP TABLE #tmpFileLines
SET NOCOUNT OFF
END
La stored procedure USP_IMPORT_XML_INTO_SQL() può essere utilizzata anche con SQL Server 2000.
