Drive.Xml

The Drive.Xml provider enables you to write a Luminesce query that extracts data from one or more XML files stored in Drive.

Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and enumerate target files and folders in Drive. This should automatically be the case if you are the domain owner.

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, Drive.Sqlite, Drive.Csv, Drive.RawText, Drive.File

Basic usage

@x = use Drive.Xml
<options>
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 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.

To see a help screen of available options, their data types, default values, and an explanation for each, run the following query using a suitable tool:

@x = use Drive.Xml
enduse;
select * from @x

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.

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 version="1.0" encoding="UTF-8"?>
<books>
    <book>
        <title lang="en" colour="blue">This is the title of book 1</title>
        <price>19.99</price>
    </book>
    <book>
        <title lang="en" colour="white">This is the title of book 2</title>
        <price>29.99</price>
    </book>
</books>

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.

@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:

Example 2: Extract data from a XML file with multiple namespaces

Consider an XML document like this stored as a file in Drive:

<?xml version="1.0" encoding="UTF-8"?>
<root xmlns:h="http://www.w3.org/schema1"
      xmlns:f="https://www.w3.org/schema2">
<h:books>
    <h:book xmlns:h="http://www.w3.org/schema1">
        <h:title lang="en" colour="blue">This is the title of book 1</h:title>
        <f:title lang="hu" colour="yellow">Book 1: The title</f:title>
        <h:price>19.99</h:price>
    </h:book>
    <h:book xmlns:h="http://www.w3.org/schema1">
        <h:title lang="en" colour="white">This is the title of book 2</h:title>
        <f:title lang="hu" colour="orange">Book 2: The title</f:title>
        <h:price>29.99</h:price>
    </h:book>
</h:books>
<f:books>
    <f:book>
        <f:title lang="hu" colour="green">Book 3: The title</f:title>
        <f:price>39.95</f:price>
    </f:book>
</f:books>
</root>

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.

@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:

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.

@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.

@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

@x = use Drive.Xml
--file=/sales/book.xml
--inferTypeRowCount=10
enduse;
select * from @x