Discussion :

[MC BAO]

salut a vous
Depuis une semaine je chercher a importer les données d'un fichiers XML vers une base de données.
Mais sans succès, j'utilise SQL-SERVER et mon fichiers XML doit charger plusieurs tables.
voici le requête que j'ai essayé mais sans succès:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE TABLE tmpXml (XmlCol XML)
INSERT INTO tmpXml(XmlCol) 
SELECT * FROM OPENROWSET(BULK 'C:\Users\chonko0\Desktop\File2.xml', SINGLE_BLOB) AS x
declare @doc nvarchar(1000)
select @doc = convert(nvarchar(1000), XmlCol) from tmpXml
SELECT Customs_offfice_code, voyage_number, Date_of_departure, Date_of_arrival, Time_of_arrival, Total_number_of_packages, Total_number_of_containers, Total_gross_mass, Mode_of_transport_code, Identity_of_transporter, Nationality_of_transporter_code, Place_of_transporter,Registration_number_of_transport_code, Date_of_registration, Master_information, Date_of_last_discharge, Carrier_code, Carrier_name, Carrier_address1, Carrier_address2, Carrier_address3, Place_of_departure_code, Place_of_destination_code, Tonnage_net_weight, Tonnage_gross_weight
 
FROM OPENXML(@doc, '\Manifeste/General_segment/General_segment_id/Total_segment/Transport_information/Carrier/Load_unjoad_place/Tonnage', 8) WITH 
(
Customs_offfice_code Nvarchar(50),
voyage_number Nvarchar(50),
Date_of_departure Datetime, 
Date_of_arrival Datetime ,
Time_of_arrival Datetime,
Total_number_of_packages Nvarchar(50),
Total_number_of_containers Nvarchar(50),
Total_gross_mass Nvarchar(50),
Mode_of_transport_code Nvarchar(50),
Identity_of_transporter Nvarchar(50),
Nationality_of_transporter_code Nvarchar(50),
Place_of_transporter Nvarchar(50),
Registration_number_of_transport_code Nvarchar(50),
Date_of_registration Nvarchar(50),
Master_information Nvarchar(50),
Date_of_last_discharge Nvarchar(50),
Carrier_code Nvarchar(50),
Carrier_name Nvarchar(50),
Carrier_address1 Nvarchar(50),
Carrier_address2 Nvarchar(50),
Carrier_address3 Nvarchar(50), 
Carrier_address4 Nvarchar(50),
Place_of_departure_code Nvarchar(50),
Place_of_destination_code Nvarchar(50),
Tonnage_net_weight Nvarchar(10),
Tonnage_gross_weight Nvarchar(10)
)
declare @ficpath Nvarchar(2000)
declare @vsql varchar(2000)
select @vsql = 'INSERT INTO tmpXml(ColXml) SELECT * FROM OPENROWSET(BULK ''' + @ficPath + ''', SINGLE_BLOB) AS x'
exec   (@vsql)

Voici mon fichier XML


Vos contributions seront les bienvenues.
Cordialement.