| Type | Read/write | Author | Availability |
| Direct provider | Read | Finbourne | Provided with LUSID |
The Tools.Parse.Xml provider enables you to write a Luminesce query that reads XML from one or more cells within a table.
Note: The LUSID user running the query must have sufficient access control permissions to use this provider. This should automatically be the case if you are the domain owner.
See also: Tools.Parse.Csv
Basic usage
@data =
select
'<filename>' as Filename,
'<?<XML-declaration>?>
<rows>
<row>
<<column-name>><some-data></<column-name>>
[<<column-name>>...]
</row>
</rows>' as Content;
@parsed =
use Tools.Parse.Xml with @input
--<optional-arguments>
enduse;
select * from @parsed;
Input tables
Tools.Parse.Xml takes in one input table and outputs a table of data, see example 1.
Options
Tools.Parse.Xml has options that enable you to refine a query.
An option takes the form --<option>=<value>, for example --fileFilter=myFile.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.
The table below provides information on the most commonly used options for Tools.Parse.Xml:
| Option | Value | Status | Information |
--fileFilter | Regex string, for example MyFile.xml, MyFile.* or File[1-3] | Optional | Based on the Filename column, selects the rows which should be processed. If the Filename column is omitted, names files sequentially as File1, File2 and so on. |
--addFileName | Boolean | Optional | Adds a column to the result set containing the file the row came from. |
--nodePath | String | Optional | XML Path query that selects the nodes to map to rows. Defaults to //*. |
To see a help screen of all available options, their data types, default values, and an explanation for each, run the following query using a suitable tool:
@x = use Tools.Parse.Xml
--help
enduse;
select * from @x;Examples
Note: For more example Luminesce queries, visit our GitHub repo.
Example 1: Reading a single XML file from a table
@data =
select
'MyFile.xml' as Filename,
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<TextColumn1>some text</TextColumn1>
<TextColumn2>more text</TextColumn2>
<NumberColumn1>1</NumberColumn1>
<NumberColumn2>2</NumberColumn2>
</row>
</rows>' as Content
;
@parsed =
use Tools.Parse.Xml with @data
--addFileName
--nodePath=/rows/row
enduse;
select * from @parsed;The table of data returned looks like this:
Example 2: Reading multiple XMLs at the same time
In this example, the --addFileName option is specified to ensure the first column in the table of results contains the filename corresponding to each row of data.
@data =
select
'MyFile.xml' as Filename,
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<TextColumn1>some text</TextColumn1>
<TextColumn2>more text</TextColumn2>
<NumberColumn1>1</NumberColumn1>
<NumberColumn2>2</NumberColumn2>
</row>
</rows>' as Content
union all
select
'MyOtherFile.xml' as Filename,
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<TextColumn1>text from my other file</TextColumn1>
<TextColumn2>more text from my other file</TextColumn2>
<NumberColumn1>10</NumberColumn1>
<NumberColumn2>20</NumberColumn2>
</row>
</rows>' as Content
union all
select
'MyThirdFile.xml' as Filename,
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<TextColumn1>some text from my third file</TextColumn1>
<TextColumn2>more text from my third file</TextColumn2>
<NumberColumn1>100</NumberColumn1>
<NumberColumn2>200</NumberColumn2>
</row>
</rows>' as Content
;
@parsed =
use Tools.Parse.Xml with @data
--addFileName
--nodePath=/rows/row
enduse;
select * from @parsed;The table of data returned looks like this:
Example 3: Returning only rows with a particular filename
In this example, the --fileFilter option is specified to only select rows with a Filename value that contains ReportFile.
@data =
select
'ReportFile_1.xml' as Filename,
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<EmployeeName>Jane Bloggs</EmployeeName>
<Id>26</Id>
</row>
</rows>' as Content
union all
select
'ReportFile_2.xml',
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<EmployeeName>Joe Bloggs</EmployeeName>
<Id>58</Id>
</row>
</rows>'
union all
select
'UnrelatedFile.xml',
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<UnrelatedColumn1>some unrelated text</UnrelatedColumn1>
<UnrelatedColumn2>some more text</UnrelatedColumn2>
</row>
</rows>'
;
@parsed =
use Tools.Parse.Xml with @data
--fileFilter=ReportFile.*
--nodePath=/rows/row
enduse;
select * from @parsed;The table of data returned looks like this, with UnrelatedFile.Xml being ignored for not matching the specified --fileFilter value:
Example 4: Returning only particular rows without any filenames
In this example, the --fileFilter option is specified to only select particular rows to process. As no Filename column is specified in @data, Tools.Parse.Xml gives each row a sequential name which can then be referred to in the --fileFilter option.
@data =
select
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<EmployeeName>Jane Bloggs</EmployeeName>
<Id>26</Id>
</row>
</rows>' as Content
union all
select
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<EmployeeName>Joe Bloggs</EmployeeName>
<Id>58</Id>
</row>
</rows>'
union all
select
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<EmployeeName>Jane Doe</EmployeeName>
<Id>73</Id>
</row>
</rows>'
union all
select
'<?xml version="1.0" encoding="utf-8"?>
<rows>
<row>
<EmployeeName>John Doe</EmployeeName>
<Id>107</Id>
</row>
</rows>'
;
@parsed =
use Tools.Parse.Xml with @data
--fileFilter=File[2,4]
--addFileName
--nodePath=/rows/row
enduse;
select * from @parsed;
The table of data returned looks like this, containing only the second and fourth rows from @data: