[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