--- title: "Drive.Xml" slug: "drivexml" tags: ["luminesce ", "drive", "provider", "xml"] updated: 2026-08-07T09:32:49Z published: 2026-08-07T09:32:49Z canonical: "support.lusid.com/drivexml" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.lusid.com/llms.txt > Use this file to discover all available pages before exploring further. # Drive.Xml | Type | Read/write | Author | Availability | | --- | --- | --- | --- | | [Direct provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider#direct-provider) | Write | FINBOURNE | Provided with LUSID | The `Drive.Xml` provider enables you to write a [Luminesce query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that extracts data from one or more XML files stored in [Drive](/v1/docs/drive). The query returns a table of data assembled from the contents of the file or files in the order they are read. **See also:** [Drive.Excel](/v1/docs/driveexcel), [Drive.Sqlite](/v1/docs/drivesqlite), [Drive.Csv](/v1/docs/drivecsv), [Drive.RawText](/v1/docs/driverawtext), [Drive.File](/v1/docs/drivefile) ## Basic usage ```sql @x = use Drive.Xml enduse; select * from @x ``` ## Options `Drive.Xml` has options that enable you to filter or refine a query. > **Note:** The `--file` option is mandatory. The `--columns` and `--nodePath` options are recommended; see below. An option takes the form `--<option>=<value>`, for example `--file=book.xml`. Note no spaces are allowed either side of the = operator. If an option: - Takes a boolean value, then specifying that option (for example `--addFileName`) sets it to True; omitting the option specifies False. - Takes multiple string values, then specify a comma-separated list, for example `--types=Boolean,Date,Decimal`. While not mandatory, note that: - The `--nodePath` option accepts an [XPath](https://www.w3schools.com/xml/xpath_syntax.asp) query that selects the node group(s) to query. If omitted, the entire root node is queried (//*). - The `--columns` option is required to actually retrieve data from an XML document (as opposed to just counting nodes). Arguments must be key/value pairs specified on separate lines underneath the option itself, where the key is the column name you want in the results table and the value is an XPath query that selects the node and/or attribute values to display. See the examples below. Current options at article update time are listed in the table below. For the very latest information, run the following query using a [suitable tool](/v1/docs/what-tools-are-available-to-write-luminesce-queries) and examine the online help: ```sql @x = use Drive.Xml enduse; select * from @x ``` | Current options | Explanation | | --- | --- | | `file` (`-f`) | **Mandatory**. The file to read. Should be text (any extension) or compressed (.zip, .gz or .tar.gz). It may also be a folder, in which case --folderFilter is also required to specify which files in the folder to process. [String] | | `folderFilter` | Denotes this is searching an entire folder structure and provides a Regular Expression of path/file names within it that should be processed. All matches should be of the same format. [String] | | `zipFilter` (`-z`) | Denotes this is a Zip file and provides a Regular Expression of path/file names within it that should be processed. All matches should be of the same format. [String] | | `allowMissing` | Should a file/folder simply not exist, don't throw an error but return an empty table with column names and types created as best possible given other options. [Boolean] | | `addFileName` | Adds a column (the first column) to the result set which contains the file the row came from. [Boolean] | | `types` | Column types (comma delimited list of: 'Boolean, Date, DateTime, Decimal, Double, Int, BigInt, Text, Table', some columns may be left blank while others are specified) [String] | | `trim` | Trim both leading and trailing whitespace when reading values [Boolean] | | `inferTypeRowCount` | If non-zero and 'types' is not specified (or not specified for some columns) this will look through N rows to attempt to work out the column types for columns not pre-specified [Int32] | | `valuesToMakeNull` | Regex of values to map to 'null' in the returned data. [String, Default: (^[nN][uU][lL]{1,2}$)\|((\r\n\|\n\|\r)$)\|(^(\r\n\|\n\|\r))\|(^\s*$)] | | `nodePath` | XPath query that selects the nodes to map to rows [String, Default: //*] | | `namespaces` | Selected prefix(es) and namespace(s): prefix1=namespace1-uri1,prefix2=namespace2-uri2,...prefixN=namespaceN-uriN [String] | | `columns` | Column names and XPath to create them, of the form: Name=XPath (one pair per line) If omitted this will be made up of all the direct children of the first selected node in the --nodePath. If any column doesn't supply XPath (so of the form: abc or abc=) then the XPath will be inferred in the same way as omitting this entirely, but only requested columns will be returned. [String] | ## Examples In the following examples, the `select * from @x` syntax at the end prints the table of data assembled by the query. > **Note:** For more examples, try the [Luminesce Github repo](https://github.com/finbourne/luminesce-examples/tree/master/examples/drive). ### Example 1: Extract data from a XML file with either one or no namespace Consider an XML document like this stored as a file in Drive: ```xml            This is the title of book 1        19.99                This is the title of book 2        29.99     ``` The `--nodePath` option queries each `<book>` node in the root `<books>` node. The `--columns` option displays the `<title>` and `<price>` node values, and the `lang` and `colour` attribute values, in four separate columns, one row per book. ```sql @x = use Drive.Xml --file=/sales/book.xml --nodePath=books/book --columns BookTitle=title BookPrice=price Language=title/@lang Color=title/@colour enduse; select * from @x ``` The table of data returned by the query looks like this: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/421d9e0f-1e9c-4384-b021-c9e9987a879e.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A00%3A25Z&se=2026-11-04T03%3A00%3A25Z&sr=c&sp=r&sig=K3CVnUYbjnSjdhleoRq4HgzqFShKdoiVrZH%2BIClMsYs%3D) ### Example 2: Extract data from a XML file with multiple namespaces Consider an XML document like this stored as a file in Drive: ```xml            This is the title of book 1        Book 1: The title        19.99                This is the title of book 2        Book 2: The title        29.99                Book 3: The title        39.95     ``` The `--nodePath` option queries each `<book>` node in the `<books>` node in the h namespace. The `--columns` option displays the `<title>` node values from both the `f` and `h` namespaces. ```sql @x = use Drive.Xml --file=/sales/book.xml --namespaces=h=http://www.w3.org/schema1,f=https://www.w3.org/schema2 --nodePath=root/h:books/h:book --columns BookTitle=h:title AltBookTitle=f:title enduse; select * from @x ``` The table of data returned by the query looks like this: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/f1188e6c-884a-445a-9112-7ecc242f2400.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A00%3A25Z&se=2026-11-04T03%3A00%3A25Z&sr=c&sp=r&sig=K3CVnUYbjnSjdhleoRq4HgzqFShKdoiVrZH%2BIClMsYs%3D) ### Example 3: Extract data from multiple XML files matched using a regular expression If `--file` is a folder in Drive, you must specify the `--folderFilter` option with a regular expression to nominate one or more files to read. In this example, data is extracted from three files `book-1.xml`, `book-2.xml` and `book-3.xml`, but not from `book-4.xml`. The `--addFileName` option adds an extra column to the table of results showing the source of each record. ```sql @x = use Drive.Xml --file=/sales --folderFilter=book-[1-3].xml --addFileName enduse; select * from @x ``` ### Example 4: Extract data from multiple XML files stored in a ZIP archive In this example, `sales.zip` is stored in the root Drive folder; data is extracted from any XML file within it that just has alphabetic characters in its file name. ```sql @x = use Drive.Xml --file=sales.zip --zipFilter=[a-zA-Z].xml --addFileName enduse; select * from @x ``` ### Example 5: Infer data types of columns from the first 10 rows ```sql @x = use Drive.Xml --file=/sales/book.xml --inferTypeRowCount=10 enduse; select * from @x ``` ### Example 6: Extract data from a repeating XML group, including a value from the parent node Consider an XML document like this stored as a file in Drive, where each `BND_CORP` node contains a `CUSIP_ALIAS_set` made up of multiple `CUSIP_ALIAS_record` nodes: ```xml          000000123                                    1111                10000001                DEBTID                                        2222                20000002                CLIENTID                                        33                US0000001234                ISIN                                    000000789                     ``` The `--nodePath` option queries each `<CUSIP_ALIAS_record>` node, rather than the `<CUSIP_ALIAS_set>` or `<BND_CORP>` node above it. This gives one row per record. Pointing `--nodePath` at a parent node instead would return a single row per parent, with the whole set of child records collapsed into one string value. `--columns` XPath is always evaluated relative to the node selected by `--nodePath`. Since the query is scoped to `<CUSIP_ALIAS_record>`, retrieving a value from further up the document (such as `<CUSIP>` on the enclosing `<BND_CORP>`) means navigating there with relative XPath. `../../CUSIP` moves up two levels, first to `<CUSIP_ALIAS_set>`, then to `<BND_CORP>`, and applies that same CUSIP value to every row produced for that parent. ```sql @x = use Drive.Xml --file=/path/to/file.xml --nodePath=ASSETS/ASSET/BND_CORP/CUSIP_ALIAS_set/CUSIP_ALIAS_record --columns CUSIP=../../CUSIP CODE=CODE IDENTIFIER=IDENTIFIER PURPOSE=PURPOSE enduse; select * from @x ``` The table of data returned by the query looks like this: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-FVG8LN3H.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A00%3A25Z&se=2026-11-04T03%3A00%3A25Z&sr=c&sp=r&sig=K3CVnUYbjnSjdhleoRq4HgzqFShKdoiVrZH%2BIClMsYs%3D) > [!NOTE] > Note > > `--nodePath` only produces rows for nodes it can match, so a parent whose `CUSIP_ALIAS_set` contains no records is omitted from the results entirely. See [Example 7](/v1/docs/drivexml#example-7-extract-data-from-a-repeating-xml-group-including-parent-rows-with-no-matching-child-records) for how to keep those parents in the output. ### Example 7: Extract data from a repeating XML group, including parent rows with no matching child records In this example, using the same XML file as [Example 6](/v1/docs/drivexml#example-6-extract-data-from-a-repeating-xml-group-including-a-value-from-the-parent-node), the parent and child nodes are queried separately. The two results sets are combined with a `left outer join` on a shared key. ```sql @parents = use Drive.Xml --file=/path/to/file.xml --nodePath=ASSETS/ASSET/BND_CORP --columns CUSIP=CUSIP enduse; @children = use Drive.Xml --file=/path/to/file.xml --nodePath=ASSETS/ASSET/BND_CORP/CUSIP_ALIAS_set/CUSIP_ALIAS_record --columns CUSIP=../../CUSIP CODE=CODE IDENTIFIER=IDENTIFIER PURPOSE=PURPOSE enduse; select p.CUSIP, c.CODE, c.IDENTIFIER, c.PURPOSE from @parents p left outer join @children c on p.CUSIP = c.CUSIP ``` The table of data returned by the query looks like this: ![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/image-HYDSRFC2.png?sv=2026-02-06&spr=https&st=2026-11-04T02%3A00%3A25Z&se=2026-11-04T03%3A00%3A25Z&sr=c&sp=r&sig=K3CVnUYbjnSjdhleoRq4HgzqFShKdoiVrZH%2BIClMsYs%3D)