title: Import structured XML file to SQL table
body: <p>I'm trying to download and import a XML file to an SQL Table.
  The download is working perfectly and it generates the following XML file.</p>
  
  <h2>GeoPers.xml</h2>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"&gt;
  &lt;soap:Body&gt;
      &lt;Clocking_GetByDateRangeUtcResponse xmlns="http://www.geodynamics.be/webservices"&gt;
          &lt;Clocking_GetByDateRangeUtcResult&gt;
              &lt;ClockingEntity&gt;
                  &lt;Id&gt;af261a77-a35b-4778-a175-6fd50d00cde1&lt;/Id&gt;
                  &lt;DateTimeUtc&gt;2017-09-28T02:37:56Z&lt;/DateTimeUtc&gt;
                  &lt;DateTimeLocal&gt;2017-09-28T04:37:56&lt;/DateTimeLocal&gt;
                  &lt;User&gt;
                      &lt;Id&gt;4975535a-2690-4492-bc7f-5e8a7d839cb9&lt;/Id&gt;
                      &lt;Name&gt;Marcel De Doncker&lt;/Name&gt;
                      &lt;DayProgramId xsi:nil="true"/&gt;
                  &lt;/User&gt;
                  &lt;Vehicle&gt;
                      &lt;Id&gt;52e01d9c-96fa-46ed-98fe-f62e1792f019&lt;/Id&gt;
                      &lt;Code&gt;7080&lt;/Code&gt;
                      &lt;Name&gt;Auto: Dacia Dokker 1-PAE-120&lt;/Name&gt;
                      &lt;VehicleTypeId xsi:nil="true"/&gt;
                      &lt;LastSyncDateUtc xsi:nil="true"/&gt;
                  &lt;/Vehicle&gt;
                  &lt;Type&gt;StartMovementDriver&lt;/Type&gt;
                  &lt;Location&gt;
                  &lt;Address&gt;
                      &lt;Street&gt;Kapelleveld&lt;/Street&gt;
                      &lt;HouseNumber&gt;49&lt;/HouseNumber&gt;
                      &lt;PostalCode&gt;1785&lt;/PostalCode&gt;
                      &lt;City&gt;Merchtem&lt;/City&gt;
                      &lt;Submunicipality&gt;Merchtem&lt;/Submunicipality&gt;
                      &lt;Country&gt;Belgium&lt;/Country&gt;
                  &lt;/Address&gt;
                  &lt;Longitude&gt;4.2380828857421875&lt;/Longitude&gt;
                  &lt;Latitude&gt;50.9540901184082&lt;/Latitude&gt;
                  &lt;/Location&gt;
                  &lt;Pois/&gt;
                  &lt;IsManual&gt;false&lt;/IsManual&gt;
              &lt;/ClockingEntity&gt;
          &lt;/Clocking_GetByDateRangeUtcResult&gt;
      &lt;/Clocking_GetByDateRangeUtcResponse&gt;
  &lt;/soap:Body&gt;
  &lt;/soap:Envelope&gt;
  </code></pre>
  
  <p>I'm using the following query to load the file into SQL:</p>
  
  <h2>SQL QUERY</h2>
  
  <pre><code>DECLARE @XmlFile XML
  
  SELECT @XmlFile = BulkColumn  
  FROM OPENROWSET(BULK 'C:\Temp\GeoDynamics\Downloads\GeoPers.xml', SINGLE_BLOB) x
  
  SELECT 
      Id = resource.value('(Id)[1]', 'varchar(255)'),
      DateTimeUtc = resource.value('(DateTimeUtc)[2]', 'varchar(255)'),
      DateTimeLocal = resource.value('(DateTimeLocal)[3]', 'varchar(255)'),
      UserID = resource.value('(User/Id)[4]', 'varchar(255)'),
      UserName = resource.value('(User/Name)[5]', 'varchar(255)'),
      VehicleID = resource.value('(Vehicle/Id)[6]', 'varchar(255)'),
      VehicleCode = resource.value('(Vehicle/Code)[7]', 'varchar(255)'),
      VehicleName = resource.value('(Vehicle/Name)[8]', 'varchar(255)')
   FROM
  @XmlFile.nodes('//Clocking_GetByDateRangeUtcResponse/Clocking_GetByDateRangeUtcResult/ClockingEntity') AS XTbl1(resource)
  </code></pre>
  
  <p>The query gives no result, not even an error code. I don't know where I'm wrong in the query.</p>