How to Import XML table Google Sheets

so I have a google sheet whereby I want to import F1 qualifying and race results via the ergast.com API. I'm only after the driver and position information from the table, but happy to import the whole table if it's easier. I've tried IMPORTHTML, IMPORTFEED, IMPORTXML but regardless of the query I make, I can't seem to generate any data (just N/A).

The API link for race 1 is: http://ergast.com/api/f1/2016/1/qualifying. URL is in cell A1.

I've tried =IMPORTHTML(A1,"list",0) and also the same with table, but nothing.

I've tried specific xpaths with =IMPORTXML too, but can't seem to get any data to show.

Please can somebody help point me in the right direction to help import this data?

Many thanks.

To import an XML file we could use IMPORTXML or IMPORTDATA.

For the case of using IMPORTXML, it's important to note on the source file if a xmlns is set. On the referred source file the third line,

<MRData xmlns="http://ergast.com/mrd/1.4" series="f1" url="http://ergast.com/api/f1/2016/1/qualifying" limit="30" offset="0" total="22">

tell us that a namespace is used (see the xmlns attribute), so we should use wildcards or query for the values of the name attribute.

As the position is an attribute of the elements named Driver, in the following example, the data is retrieved by using to IMPORTXML and arrays:

Example
A1: http://ergast.com/api/f1/2016/1/qualifying
A2: Driver
A3:

={
  IMPORTXML(A1,"//*[name()='"&A2&"']/../@position"),
  IMPORTXML(A1,"//*[name()='"&A2&"']")
}

NOTE: Some spreadsheet settings require the use of \ instead of , as column separator

Result

+----+--------+----+-----------+------------+------------+------------+
|    |   A    | B  |     C     |     D      |     E      |     F      |
+----+--------+----+-----------+------------+------------+------------+
|  1 | http://ergast.com/api/f1/2016/1/qualifying        |            |
|  2 | Driver |    |           |            |            |            |
|  3 | 1      | 44 | Lewis     | Hamilton   | 1985-01-07 | British    |
|  4 | 2      |  6 | Nico      | Rosberg    | 1985-06-27 | German     |
|  5 | 3      |  5 | Sebastian | Vettel     | 1987-07-03 | German     |
|  6 | 4      |  7 | Kimi      | Räikkönen  | 1979-10-17 | Finnish    |
|  7 | 5      | 33 | Max       | Verstappen | 1997-09-30 | Dutch      |
|  8 | 6      | 19 | Felipe    | Massa      | 1981-04-25 | Brazilian  |
|  9 | 7      | 55 | Carlos    | Sainz      | 1994-09-01 | Spanish    |
| 10 | 8      |  3 | Daniel    | Ricciardo  | 1989-07-01 | Australian |
| 11 | 9      | 11 | Sergio    | Pérez      | 1990-01-26 | Mexican    |
| 12 | 10     | 27 | Nico      | Hülkenberg | 1987-08-19 | German     |
| 13 | 11     | 77 | Valtteri  | Bottas     | 1989-08-29 | Finnish    |
| 14 | 12     | 14 | Fernando  | Alonso     | 1981-07-29 | Spanish    |
| 15 | 13     | 22 | Jenson    | Button     | 1980-01-19 | British    |
| 16 | 14     | 30 | Jolyon    | Palmer     | 1991-01-20 | British    |
| 17 | 15     | 20 | Kevin     | Magnussen  | 1992-10-05 | Danish     |
| 18 | 16     |  9 | Marcus    | Ericsson   | 1990-09-02 | Swedish    |
| 19 | 17     | 12 | Felipe    | Nasr       | 1992-08-21 | Brazilian  |
| 20 | 18     | 26 | Daniil    | Kvyat      | 1994-04-26 | Russian    |
| 21 | 19     |  8 | Romain    | Grosjean   | 1986-04-17 | French     |
| 22 | 20     | 21 | Esteban   | Gutiérrez  | 1991-08-05 | Mexican    |
| 23 | 21     | 88 | Rio       | Haryanto   | 1993-01-20 | Indonesian |
| 24 | 22     | 94 | Pascal    | Wehrlein   | 1994-10-18 | German     |
+----+--------+----+-----------+------------+------------+------------+