Ivan Pudic Ivan Pudic - 3 years ago 112
SQL Question

sql import xml file with multiple nodes/children

I'm trying to import the following XML file which contains multiple nodes on the same ID. It's a GPS output from a car (id and name). The file contains a node TripItemEntities which contains multiple TripItemEntity.

GeoBewegingen.xml (file to import)



<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<TripOverview_GetByVehicleIdListDateResponse xmlns="http://www.geodynamics.be/webservices">
<TripOverview_GetByVehicleIdListDateResult>
<TripOverviewEntity>
<Vehicle>
<Id>a58f2d56-2b07-41d4-ada1-00a7ca934868</Id>
<Name>1SNG489</Name>
</Vehicle>
<FromDateUtc>2017-09-28T22:00:00</FromDateUtc>
<ToDateUtc>2017-09-29T21:59:59</ToDateUtc>
<FromDateLocal>2017-09-29T00:00:00</FromDateLocal>
<ToDateLocal>2017-09-29T23:59:59</ToDateLocal>
<TripItemEntities>
<TripItemEntity>
<FromDateUtc>2017-09-28T22:00:00</FromDateUtc>
<ToDateUtc>2017-09-29T03:58:21</ToDateUtc>
<FromDateLocal>2017-09-29T00:00:00</FromDateLocal>
<ToDateLocal>2017-09-29T05:58:21</ToDateLocal>
<FromLocation>
<Address>
<Street>Merkenveld</Street>
<HouseNumber>31</HouseNumber>
<PostalCode>2870</PostalCode>
<City>Puurs</City>
<Submunicipality>Puurs</Submunicipality>
<Country>Belgium</Country>
</Address>
<Longitude>4.28923606872559</Longitude>
<Latitude>51.0761642456055</Latitude>
</FromLocation>
<ToLocation>
<Address>
<Street>Merkenveld</Street>
<HouseNumber>31</HouseNumber>
<PostalCode>2870</PostalCode>
<City>Puurs</City>
<Submunicipality>Puurs</Submunicipality>
<Country>Belgium</Country>
</Address>
<Longitude>4.28923606872559</Longitude>
<Latitude>51.0761642456055</Latitude>
</ToLocation>
<Type>StandStill</Type>
<FromPois />
<ToPois />
<MileageDriven>0</MileageDriven>
<MileageBirdFlight>0</MileageBirdFlight>
<Users />
</TripItemEntity>
<TripItemEntity>
<FromDateUtc>2017-09-29T03:58:21</FromDateUtc>
<ToDateUtc>2017-09-29T04:43:21</ToDateUtc>
<FromDateLocal>2017-09-29T05:58:21</FromDateLocal>
<ToDateLocal>2017-09-29T06:43:21</ToDateLocal>
<FromLocation>
<Address>
<Street>Merkenveld</Street>
<HouseNumber>31</HouseNumber>
<PostalCode>2870</PostalCode>
<City>Puurs</City>
<Submunicipality>Puurs</Submunicipality>
<Country>Belgium</Country>
</Address>
<Longitude>4.28923606872559</Longitude>
<Latitude>51.0761642456055</Latitude>
</FromLocation>
<ToLocation>
<Address>
<Street>Hazopweg</Street>
<PostalCode>9130</PostalCode>
<City>Beveren</City>
<Submunicipality>Kallo</Submunicipality>
<Country>Belgium</Country>
</Address>
<Longitude>4.22500610351563</Longitude>
<Latitude>51.2515640258789</Latitude>
</ToLocation>
<Type>Driving</Type>
<FromPois />
<ToPois />
<MileageDriven>26.60009765625</MileageDriven>
<MileageBirdFlight>20.0111999723037</MileageBirdFlight>
<Users>
<UserEntity>
<Id>60f0691d-8348-42cf-8ce9-2e1744b0115e</Id>
<Name>Thomas Jacobs</Name>
<DayProgramId xsi:nil="true" />
</UserEntity>
</Users>
</TripItemEntity>
</TripItemEntities>
</TripOverviewEntity>
</TripOverview_GetByVehicleIdListDateResult>
</TripOverview_GetByVehicleIdListDateResponse>
</soap:Body>
</soap:Envelope>


I'm using the following SQL to import the XML, but i only get the first TripItemEntity, not the rest.

SQL Query



DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn
FROM OPENROWSET(BULK 'C:\Temp\GeoDynamics\Downloads\GeoBewegingen.xml', SINGLE_BLOB) x;

WITH XMLNAMESPACES (DEFAULT 'http://www.geodynamics.be/webservices')
INSERT INTO GeoBewegingen(Id,voernaam,voercode,pervan,pertot,tripvan,triptot,tripvanlocstr,tripvanlocnr,tripvanlocpc,tripvanloccity,tripvanlocsub,tripvanloccountry,tripvanlong,tripvanlat,tripnaarlocstr,tripnaarlocnr,tripnaarlocpc,tripnaarloccity,tripnaarlocsub,tripnaarloccountry,tripnaarlong,tripnaarlat,triptype,tripkmgereden,tripkmvogelvlucht,tripuserid,tripusername)
SELECT
Id = resource.value('(Vehicle/Id)[1]', 'varchar(255)'),
voernaam = resource.value('(Vehicle/Name)[1]', 'varchar(255)'),
voercode = resource.value('(Vehicle/Code)[1]', 'varchar(255)'),
pervan = resource.value('(FromDateLocal)[1]', 'varchar(255)'),
pertot = resource.value('(ToDateLocal)[1]', 'varchar(255)'),
tripvan = resource.value('(TripItemEntities/TripItemEntity/FromDateLocal)[1]', 'varchar(255)'),
triptot = resource.value('(TripItemEntities/TripItemEntity/ToDateLocal)[1]', 'varchar(255)'),
tripvanlocstr = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Address/Street)[1]', 'varchar(255)'),
tripvanlocnr = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Address/HouseNumber)[1]', 'varchar(255)'),
tripvanlocpc = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Address/PostalCode)[1]', 'varchar(255)'),
tripvanloccity = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Address/City)[1]', 'varchar(255)'),
tripvanlocsub = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Address/Submunicipality)[1]', 'varchar(255)'),
tripvanloccountry = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Address/Country)[1]', 'varchar(255)'),
tripvanlong = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Longitude)[1]', 'varchar(255)'),
tripvanlat = resource.value('(TripItemEntities/TripItemEntity/FromLocation/Latitude)[1]', 'varchar(255)'),
tripnaarlocstr = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Address/Street)[1]', 'varchar(255)'),
tripnaarlocnr = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Address/HouseNumber)[1]', 'varchar(255)'),
tripnaarlocpc = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Address/PostalCode)[1]', 'varchar(255)'),
tripnaarloccity = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Address/City)[1]', 'varchar(255)'),
tripnaarlocsub = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Address/Submunicipality)[1]', 'varchar(255)'),
tripnaarloccountry = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Address/Country)[1]', 'varchar(255)'),
tripnaarlong = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Longitude)[1]', 'varchar(255)'),
tripnaarlat = resource.value('(TripItemEntities/TripItemEntity/ToLocation/Latitude)[1]', 'varchar(255)'),
triptype = resource.value('(TripItemEntities/TripItemEntity/Type)[1]', 'varchar(255)'),
tripkmgereden = resource.value('(TripItemEntities/TripItemEntity/MileageDriven)[1]', 'varchar(255)'),
tripkmvogelvlucht = resource.value('(TripItemEntities/TripItemEntity/MileageBirdFlight)[1]', 'varchar(255)'),
tripuserid = resource.value('(TripItemEntities/TripItemEntity/Users/UserEntity/Id)[1]', 'varchar(255)'),
tripusername = resource.value('(TripItemEntities/TripItemEntity/Users/UserEntity/Name)[1]', 'varchar(255)')
FROM
@XmlFile.nodes('//TripOverview_GetByVehicleIdListDateResponse/TripOverview_GetByVehicleIdListDateResult/TripOverviewEntity') AS XTbl1(resource)


How can i import the rest of the TripItemEntity in the file?

Answer Source

You should use path of repeated nodes in nodes function.

In this question the path should be //TripOverview_GetByVehicleIdListDateResponse/TripOverview_GetByVehicleIdListDateResult/TripOverviewEntity/TripItemEntities/TripItemEntity

Fixed query:

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn  
FROM OPENROWSET(BULK 'C:\Temp\GeoBewegingen.xml', SINGLE_BLOB) x;

WITH XMLNAMESPACES (DEFAULT 'http://www.geodynamics.be/webservices')
INSERT INTO GeoBewegingen(Id,voernaam,voercode,pervan,pertot,tripvan,triptot,tripvanlocstr,tripvanlocnr,tripvanlocpc,tripvanloccity,tripvanlocsub,tripvanloccountry,tripvanlong,tripvanlat,tripnaarlocstr,tripnaarlocnr,tripnaarlocpc,tripnaarloccity,tripnaarlocsub,tripnaarloccountry,tripnaarlong,tripnaarlat,triptype,tripkmgereden,tripkmvogelvlucht,tripuserid,tripusername)
SELECT
    Id = resource.value('(../../Vehicle/Id)[1]', 'varchar(255)'),
    voernaam = resource.value('(../../Vehicle/Name)[1]', 'varchar(255)'),
    voercode = resource.value('(../../Vehicle/Code)[1]', 'varchar(255)'),
    pervan = resource.value('(../../FromDateLocal)[1]', 'varchar(255)'),
    pertot = resource.value('(../../ToDateLocal)[1]', 'varchar(255)'),
    tripvan = resource.value('(../../FromDateLocal)[1]', 'varchar(255)'),
    triptot = resource.value('(../../ToDateLocal)[1]', 'varchar(255)'),
    tripvanlocstr = resource.value('(FromLocation/Address/Street)[1]', 'varchar(255)'),
    tripvanlocnr = resource.value('(FromLocation/Address/HouseNumber)[1]', 'varchar(255)'),
    tripvanlocpc = resource.value('(FromLocation/Address/PostalCode)[1]', 'varchar(255)'),
    tripvanloccity = resource.value('(FromLocation/Address/City)[1]', 'varchar(255)'),
    tripvanlocsub = resource.value('(FromLocation/Address/Submunicipality)[1]', 'varchar(255)'),
    tripvanloccountry = resource.value('(FromLocation/Address/Country)[1]', 'varchar(255)'),
    tripvanlong = resource.value('(FromLocation/Longitude)[1]', 'varchar(255)'),
    tripvanlat = resource.value('(FromLocation/Latitude)[1]', 'varchar(255)'),
    tripnaarlocstr = resource.value('(ToLocation/Address/Street)[1]', 'varchar(255)'),
    tripnaarlocnr = resource.value('(ToLocation/Address/HouseNumber)[1]', 'varchar(255)'),
    tripnaarlocpc = resource.value('(ToLocation/Address/PostalCode)[1]', 'varchar(255)'),
    tripnaarloccity = resource.value('(ToLocation/Address/City)[1]', 'varchar(255)'),
    tripnaarlocsub = resource.value('(ToLocation/Address/Submunicipality)[1]', 'varchar(255)'),
    tripnaarloccountry = resource.value('(ToLocation/Address/Country)[1]', 'varchar(255)'),
    tripnaarlong = resource.value('(ToLocation/Longitude)[1]', 'varchar(255)'),
    tripnaarlat = resource.value('(ToLocation/Latitude)[1]', 'varchar(255)'),
    triptype = resource.value('(Type)[1]', 'varchar(255)'),
    tripkmgereden = resource.value('(MileageDriven)[1]', 'varchar(255)'),
    tripkmvogelvlucht = resource.value('(MileageBirdFlight)[1]', 'varchar(255)'),
    tripuserid = resource.value('(Users/UserEntity/Id)[1]', 'varchar(255)'),
    tripusername = resource.value('(Users/UserEntity/Name)[1]', 'varchar(255)')
FROM
    @XmlFile.nodes('//TripOverview_GetByVehicleIdListDateResponse/TripOverview_GetByVehicleIdListDateResult/TripOverviewEntity/TripItemEntities/TripItemEntity') AS XTbl1(resource)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download