Member Avatar for krofox

I have been trying since long to extract data using xpath in google sheets but everytime 'am getting message 'imported content is empty'. i need help . The url is http://reports.ieso.ca/public/IntertieScheduleFlow/PUB_IntertieScheduleFlow.xml

  • 2 Contributors
  • 4 Replies
  • 390 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by xml_looser

Dani AI

Generated

Good spot by — the XML in this thread uses a default namespace, and Google Sheets’ IMPORTXML does not let you register namespace prefixes. That’s why prefix-based XPaths (and straightforward paths) often return the “Imported content is empty” message. A reliable workaround is to write namespace‑agnostic XPath using local-name() so nodes match by their local tag name regardless of namespace.

Try this pattern in a sheet cell (replace YOUR_XML_URL with the report URL used in the thread and adjust the numeric indexes for the zone/hour you want):

=IMPORTXML("YOUR_XML_URL", "//*[local-name()='IMODocBody']/*[local-name()='IntertieZone'][1]/*[local-name()='Schedules']/*[local-name()='Schedule'][1]/*[local-name()='Import']/text()")

Why this works: local-name() matches element names without their namespace, so the query finds the same elements is targeting but ignores the default namespace that otherwise blocks simple XPath queries.

If IMPORTXML still returns empty, check these things: open the XML URL directly in a browser to confirm it returns raw XML (no auth, no redirects); some servers block Google’s fetcher or return an HTML/XSL view instead of XML; Google caches results and can be rate‑limited. When IMPORTXML can’t pull the file, use Apps Script to fetch and parse the XML (script runs with your account and can handle the namespace-neutral search). Example that collects every <Import> text and writes it to the active sheet:

function writeImportValues() {
  var url = 'YOUR_XML_URL';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var doc = XmlService.parse(xml);
  var root = doc.getRootElement();
  var out = [];
  function walk(el) {
    if (el.getName && el.getName() === 'Import') out.push([el.getText()]);
    var children = el.getChildren();
    for (var i=0;i<children.length;i++) walk(children[i]);
  }
  walk(root);
  if (out.length) SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,1,out.length,1).setValues(out);
}

Run that from the Script editor (authorize on first run). Between ’s namespace diagnosis and this namespace‑agnostic XPath / script approach, you should be able to extract the Import values reliably.

Recommended Answers

All 4 Replies

Member Avatar for krofox

To add further to my earlie question ...the page source shows

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="http://reports.ieso.ca/docrefs/stylesheet/IntertieScheduleFlow_HTML_t1-2.xsl" ?><IMODocument docID="IntertieScheduleFlow" xmlns="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation=" http://reports.ieso.ca/docrefs/schema/IntertieScheduleFlow_r2.xsd">
<IMODocHeader>
<DocTitle>Intertie Schedule and Flow Report</DocTitle>
<DocRevision>2</DocRevision>
<DocConfidentiality>
<DocConfClass>PUB</DocConfClass>
</DocConfidentiality>
<CreatedAt>2015-08-22T15:30:46</CreatedAt>
</IMODocHeader>
<IMODocBody>
<Date>2015-08-22</Date>
<IntertieZone>
<IntertieZoneName>MANITOBA</IntertieZoneName>
<Schedules>
<Schedule>
<Hour>1</Hour>
<Import>30</Import>
</Schedule>

And i tried the following xpath
/IMODocument/IMODocBody/IntertieZone[1]/Schedules/Schedule[1]

I would like to extract the value of Import 30 ;
I think i am clear in explaning my problem a bit in detail
Thanks

Member Avatar for xml_looser

Hello unprecedented way

Your Probelm is handling namespace

to you shall define a namespace
and then start with XPath

example

from Google XML file

xmlns = ""

So give to name as a parameter

or as a variable

ns1 = ""

I choose the second path

so should be used with

// ns1: Export

all exports are found

now work with square brackets

Member Avatar for xml_looser

yes that's correctly define ns1
and then use
with // ns1: Export
38 entries found

I have the tool stylus used
and an XPath query started
and without namespace gets no result

to test create a local file on the hard disk
and the namespace xmlns = "" remove
then test without namespace // Export

based on Example

// ns1: IMODocBody / ns1: IntertieZone [2] / ns1: schedules / ns1: Schedule / ns1: Export

0 results

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.