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) |
Partager