Wolfhart wrote:Now, I want to pull data from a local xml file, but cannot manage to figure out how to do that. My question, in short, is this: With what do I have to replace WEBSERVICE("http://www.example.com/data.xml") in the [FILTERXML functioon] if the file data.xml from which I want to pull data is a local file that is stored in the same directory as the spreadsheet?
I run OpenOffice, not LibreOffice, so I cannot do any testing, but I can offer some ideas.
I believe the WEBSERVICE function only supports the HTTP: and HTTPS: protocols, and in particular does not support FILE: so one cannot use WEBSERVICE to retrieve local data unless you create a local webserver to access it. You can do that, of course, by installing and configuring
Apache HTTP Server on your machine or in your network. However this could be more involved that you'd want. Apache HTTP Server has their own
support pages.
FILTERXML needs the XML string for the first operand.
• You could open your local file with Writer, copy the data to the clipboard, paste that data into a cell, then specify the cell address as the first FILTERXML operand. Re-copy/paste if the data changes. You might run into limits concerning the amount of data in a single cell. In that case use multiple cells and concatenate them for FILTERXML.
• You could use formula
=T('file://{YourFileName}'#$Sheet1.A1) to load the data into a cell in the sheet with the FILTERXML formula. This is good if the XML data is a single line, because all the XML will be loaded into a single cell. Specify the cell address for FILTERXML.
• For a single line XML file you could use Insert → Sheet from file → Separated by → {uncheck everything} → Column type → Text → OK → ✓ Link → OK. Specify the new sheet and cell address A1 for FILTERXML.
• If the XML file has multiple lines you could open it with Writer and condense it into a single line. You might run into limits concerning the number of characters in a single paragraph.
* If the XML file has multiple lines you can use the previous two ideas, but for the first you'll need to fill the formula down as needed to load all the lines. In either case you'll need to concatenate the cells for FILTERXML. This will be tricky if you don't know in advance the line count of the XML file.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6. The locale for any menus or Calc formulas in my posts is English (USA).