Home > Scripts > Importazione dati da file XML a tabella SQL Server

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.

TestTab.xml

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(‘</DATA>’, 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 + ‘</DATA>’
      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 = ‘<DATA>’ + @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.

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

sp_alter_column – La stored procedure per modificare una colonna!

La stored procedure sp_alter_column permette di modificare agevolmente il tipo di dato ed il nome di una colonna!