[SQL Server] Intégration d'un fichier XML dans une table

-- =============================================

-- Author:       Zouhaier KHARROUBI

-- Create date: 08/03/2018

-- Description:   Intégration d'un fichier XML dans une table

/* Le format du fichier XML à intégrer dans la table SQL Server

<?xml version="1.0" encoding="ISO-8859-1"?>

<Commandes Numclient="123456" MsgID="00000000056031702018030518999">

<Commande Devise="EUR">

   <Entete>

     <Titre>11587712</Titre>

     <DateCommande>20180305</DateCommande>

     <DateLivraison>20180305</DateLivraison>

     <LivreA>

       <Addresse>

         <Nom1>MEKTABA.iINFO</Nom1>

         <Nom2>

         </Nom2>

         <Nom3>1 rue mektaba.info</Nom3>

         <Nom4>immeuble MEKTABA.INFO 1er etage </Nom4>

         <CodePostal>75000</CodePostal>

         <Ville>PARIS</Ville>

         <Pays>FR</Pays>

         <NomContact>

         </NomContact>

         <TelephoneContact>0601020304</TelephoneContact>

         <MailContact>Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.;;/MailContact>

       </Addresse>

     </LivreA>

     <Livraison Type="CHRONOPOSTE" Full="n" />

     <CommandeOrigine>1385</CommandeOrigine>

     <TexteLibre Type="info">

     </TexteLibre>

     <ShipTxt>S33826V</ShipTxt>

   </Entete>

   <Detail>

     <Ligne ID="20">

       <ElementID>GSADA710NOIR</ElementID>

       <Quantite>10</Quantite>

       <Prix>25.05</Prix>

       <CommandeOrigine>X90957B</CommandeOrigine>

     </Ligne>

   </Detail>

</Commande>

</Commandes>

*/

-- =============================================

BEGIN

     SET NOCOUNT ON;

     IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeFichiersXMLTemp]'))

           BEGIN

                 DROP TABLE #ListeFichiersXMLTemp

           END  

     IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeFichiersXML]'))

           BEGIN

                 DROP TABLE #ListeFichiersXML

           END  

                      

     CREATE TABLE #ListeFichiersXMLTemp

     (

           NomFichier VARCHAR(250)

     )

     CREATE TABLE #ListeFichiersXML

     (

           Num_Lig       INT IDENTITY(1,1)

           ,NomFichier   VARCHAR(250)

     )    

   -- Insert statements for procedure here

   DECLARE @NomFichier           VARCHAR(250)

                 ,@Num_Lig               INT

                 ,@Nmbre_Lig             INT

                 ,@FICHIER_XML               XML

                 ,@Chemin_Commande       NVARCHAR(500) = 'D:\MES_FICHIERS_XML\Extract\Commande\MEKTABA\'

                 ,@Chemin_ARCHIVE NVARCHAR(500) = 'D:\MES_FICHIERS_XML\Extract\Commande\MEKTABA\ARCHIVE\'

                 ,@Requete               NVARCHAR(1500)

                 ,@Id_Doc               INT

  

   --Remonter les commandes trouvées sur le FTP MEKTABA

   EXEC [dbo].[usp_FTP_Importer]@ConnectionStringFTP ='ftp.mektaba.info'

           ,@ServerUserNameFTP='MonLoginFTP'

           ,@PasswordFTP='MonPasswordFTP'

           ,@PathFTP='/FICHIERS_XML/'

           ,@FichierFTP='*.xml'

           ,@SupprimerFichierFTP=1

           ,@CheminFichiersTransferes='D:\MES_FICHIERS_XML\Extract\Commande\MEKTABA'

  

  

   SELECT @Requete = N'DIR ' + @Chemin_Commande + 'FICHIERS_*.xml /B'

   INSERT INTO #ListeFichiersXMLTemp

     EXEC Master..xp_cmdshell@Requete

    

     INSERT INTO #ListeFichiersXML(NomFichier)

     SELECT NomFichier FROM #ListeFichiersXMLTemp WHERE NomFichier IS NOT NULL

    

     SELECT @Nmbre_Lig = COUNT(*),@Num_Lig = 1 FROM #ListeFichiersXML

    

     WHILE @Num_Lig <= @Nmbre_Lig

           BEGIN

                 BEGIN TRY

                       SELECT @NomFichier = NomFichier FROM #ListeFichiersXML WHERE Num_Lig = @Num_Lig

                      

                       SELECT @Requete = N'SELECT @FICHIER_XML_Out = CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(Bulk ''' + @Chemin_Commande + @NomFichier + ''', SINGLE_BLOB) [Commande]'

                       EXECUTE sp_executesql@Requete, N'@FICHIER_XML_Out XML OUTPUT', @FICHIER_XML_Out = @FICHIER_XML OUTPUT

                      

                       EXEC sp_xml_preparedocument@Id_Doc OUTPUT, @FICHIER_XML

                      

                       IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Donnees]'))

                             BEGIN

                                   DROP TABLE #Donnees

                             END  

                       SELECT * INTO #Donnees FROM OPENXML(@Id_Doc,'/Commandes/Commande/Detail/Ligne',2)

                       WITH (NumClient varchar(40) '//@NumClient',

                             MsgID varchar(30) '//@MsgID',

                             Devise varchar(3) '//@Devise',

                             Titre varchar(30) '//Titre',

                             DateCommande varchar(8) '//DateCommande',

                             DateLivraison varchar(8) '//DateLivraison',

                             Nom1varchar(35) '//Nom1',

                             Nom2varchar(35) '//Nom2',

                             Nom3varchar(35) '//Nom3',

                             Nom4varchar(35) '//Nom4',

                           Nom5varchar(35) '//Nom5',

                             CodePostalvarchar(9) '//CodePostal',

                             Ville   varchar(35) '//Ville',

                             Pays   varchar(250) '//Pays',

                             NomContact   varchar(250) '//NomContact',

                             TelephoneContact   varchar(250) '//TelephoneContact',

                             MailContact   varchar(250) '//MailContact',

                             Livraison_Type   varchar(250) '//Livraison/@Type',

                             Livraison_Full   varchar(250) '//Livraison/@Full',

                             Livraison_Desc   varchar(250) '//Livraison',

                            CommandeOrigine_Entete   varchar(250) '//CommandeOrigine',

                             TexteLibre_Type   varchar(250) '//TexteLibre/@Type',

                             TexteLibre_Desc   varchar(250) '//TexteLibre',

                             ShipTxt   varchar(250) '//ShipTxt',

                             Ligne_ID   varchar(250) '@ID',

                             Code_Article_Vendeur       varchar(250) './ElementID',

                             Code_Article_Acheteur       varchar(250) '//AddElementID/@Buyer',

                             Code_EAN_Article       varchar(250) '//AddElementID/@EAN',

                             Quantite_Commandee         varchar(250) './Quantite',

                             Prix       varchar(250) './Prix',

                             CommandeOrigine_Ligne     varchar(250) './CommandeOrigine'

                             )

                

                 TRUNCATE TABLE XML_Temp

                

                 --Créer l'entete de la commande

                 INSERT INTO XML_Temp(Type_Lig,Date_Export,Num_Client,Nom_Client,Fichier_Source,Num_Commande,Date_Commande

                 ,Date_Livraison_Debut,Date_Livraison_Fin,Date_Enlevement,Num_Contrat,Code_Vendeur

                 ,Code_Acheteur,Code_Livre,Nom_Livre,Adresse_1_Livre,Adresse_2_Livre

                 ,Ville_Livre,Code_Postal_Livre,Code_Pays_Livre,Telephone_Livre,Mail_Livre

                 ,Code_Facture,Nom_Facture,Adresse_1_Facture,Adresse_2_Facture,Code_Postal_Facture,Ville_Facture,Code_Pays_Facture

                       ,Devise,Commentaires,Num_Commande_Magasin

                       ,Ligne_Commande                                                                                                                                                                      

                 )

                 SELECT TOP 1 'ENT',GETDATE() Date_Export,'010203' Num_Client,'MEKTABA' Nom_Client,@NomFichier Fichier_Source

                 ,Titre Num_Commande

                 ,DateCommande Date_Commande

                 ,DateLivraison Date_Livraison_Debut,NULL Date_Livraison_Fin,NULL Date_Enlevement,'' Num_Contrat,(SELECT TOP 1 D_Commentaire FROM MaBaseSage..P_DOSSIER WHERE D_RaisonSoc='MASOCIETE') Code_Vendeur

                 ,NumClient Code_Acheteur,'' Code_Livre,NomContact Nom_Livre, SUBSTRING(ISNULL(Nom1,'') + ' '   + ISNULL(Nom2,''),1,35) Adresse_1_Livre, SUBSTRING(ISNULL(Nom3,'') + ' '   + ISNULL(Nom4,'') + ' '+ ISNULL(Nom5,''),1,35) Adresse_2_Livre

                 ,Ville Ville_Livre ,CodePostal Code_Postal_Livre, Pays Code_Pays_Livre,TelephoneContact AS Telephone_Livre,MailContact AS Mail_Livre

                 ,'' Code_Facture,'' Nom_Facture,'' Adresse_1_Facture,'' Adresse_2_Facture,'' Code_Postal_Facture,'' Ville_Facture,'' Code_Pays_Facture

                 ,Devise Devise, NULL Commentaires, SUBSTRING(CommandeOrigine_Entete,1,35) Num_Commande_Magasin

                 ,CAST(@FICHIER_XML AS VARCHAR(MAX)) Ligne_Commande

                 FROM #Donnees

                                  

                 --Créer les lignes de la commande

                 INSERT INTO XML_Temp(Type_Lig,Date_Export,Num_Client,Nom_Client,Fichier_Source,Num_Commande,Date_Commande

                 ,Num_Ligne_Article,Code_EAN_Article,Code_Article_Vendeur,Code_Article_Acheteur,Quantite_Commandee,Prix_Unitaire_Net

                 )

                 SELECT 'LIG',GETDATE() Date_Export,'010203' Num_Client,'MEKTABA' Nom_Client,@NomFichier Fichier_Source

                 ,Titre Num_Commande

                 , DateCommande Date_Commande

                 ,Ligne_ID Num_Ligne_Article,Code_EAN_Article,Code_Article_Vendeur,Code_Article_Acheteur,Quantite_Commandee,Prix Prix_Unitaire_Net

                 FROM #Donnees Commande BY Ligne_ID

                

                 UPDATE T1

                 SET

                       Code_EAN_Article=T2.AF_CodeBarre

                 FROM XML_Temp T1

                 LEFT JOIN MaBaseSage..F_ARTFOURNISS T2 ON T2.AR_Ref=T1.Code_Article_Vendeur AND T2.AF_Principal=1

                 WHERE ISNULL(Code_Article_Vendeur,'')<>''

                

                 END TRY

                 BEGIN CATCH

                       IF @@ERROR<>0

                             BEGIN

                                   UPDATE dbo.XML_Temp

                                   SET

                                         STATUT_Commande='E'

                                         ,Message_Erreur = ERROR_MESSAGE()                              

                             END

                             select ERROR_MESSAGE()

                 END CATCH

                 IF NOT EXISTS(SELECT * FROM dbo.XML_Temp WHERE STATUT_Commande='E')

                       BEGIN

                             UPDATE dbo.XML_Temp

                             SET

                                   STATUT_Commande='Y'

                       END

                

                 --Intéger le fichier XML dans la table Finale

                 INSERT INTO SAS_FICHIERS_XML

                 SELECT

                             Num_Lig

                       ,Type_Lig

                       ,Date_Export

                       ,Num_Client

                       ,Nom_Client

                       ,Fichier_Source

                       ,Num_Commande

                       ,Date_Commande

                       ,Date_Livraison_Debut

                       ,Date_Livraison_Fin

                       ,Date_Enlevement

                       ,Num_Contrat

                       ,Code_Vendeur

                       ,Nom_Vendeur

                       ,Adresse_1_Vendeur

                       ,Adresse_2_Vendeur

                       ,Code_Postal_Vendeur

                       ,Ville_Vendeur

                       ,Code_Pays_Vendeur

                       ,Code_Acheteur

                       ,Nom_Acheteur

                       ,Adresse_1_Acheteur

                       ,Adresse_2_Acheteur

                       ,Code_Postal_Acheteur

                       ,Ville_Acheteur

                       ,Code_Pays_Acheteur

                       ,Code_Livre

                       ,Nom_Livre

                       ,Adresse_1_Livre

                       ,Adresse_2_Livre

                        ,Code_Postal_Livre

                       ,Ville_Livre

                       ,Code_Pays_Livre

                       ,Telephone_Livre

                       ,TelephonePortable_Livre

                       ,Mail_Livre

                       ,Code_Facture

                       ,Nom_Facture

                       ,Adresse_1_Facture

                       ,Adresse_2_Facture

                       ,Code_Postal_Facture

                       ,Ville_Facture

                       ,Code_Pays_Facture

                       ,Devise

                       ,Commentaires

                       ,Num_Ligne_Article

                       ,Code_EAN_Article

                       ,Code_Article_Vendeur

                      ,Code_Article_Acheteur

                       ,Quantite_Commandee

                       ,Unite_Mesure_1

                       ,Quantite_Par_Colis_PCB

                       ,Unite_Mesure_2

                       ,Description_Article

                       ,Prix_Unitaire_Net

                       ,Montant_Net_Ligne

                       ,Nombre_UL

                       ,Type_Emballage

                       ,Code_EAN_UL_DUN_14

                       ,Date_Livraison_Lig

                       ,Num_Commande_Magasin

                       ,EAN_Magasin

                       ,Famille_Article

                       ,Poste_Livraison_Commande

                       ,Ligne_Commande

                       ,Message_Erreur

                       ,STATUT_Commande

                 FROM dbo.XML_Temp

                

                 TRUNCATE TABLE dbo.XML_Temp

                            

                 --Archiver le fichier XML traité

                 SELECT @Requete = N'MOVE /Y "' + @Chemin_Commande + @NomFichier + '" "' + @Chemin_ARCHIVE + '"'

                 EXEC Master..xp_cmdshell@Requete

                      

                 SELECT @Num_Lig = @Num_Lig + 1    

           END

    

END