CODEWITHSUNDEEP
xmlxpathgoogle-sheets
user3623246
user3623246

Reputation: 23

A Simple Xpath to Google Spreadsheet ImportXML

This is the XML I want to import from: http://api.eve-marketdata.com/api/item_history2.xml?char_name=demo&region_ids=10000002&type_ids=16669&days=3

These are the XML contents returned by the URL above:

<emd version="2">
    <currentTime>2014-05-10 13:17:27</currentTime>
    <result>
        <rowset name="history" key="typeID,regionID,date" columns="typeID,regionID,date,lowPrice,highPrice,avgPrice,volume,orders">
            <row typeID="16669" regionID="10000002" date="2014-05-08" lowPrice="27448" highPrice="37447.89" avgPrice="31586.6243" volume="156980" orders="24"/>
            <row typeID="16669" regionID="10000002" date="2014-05-09" lowPrice="30501.17" highPrice="37445" avgPrice="36530.1858" volume="170121" orders="16"/>
            <row typeID="16669" regionID="10000002" date="2014-05-10" lowPrice="33004" highPrice="33004.03" avgPrice="33004.0224" volume="40425" orders="3"/>
        </rowset>
    </result>
</emd>

But since it isn't a gray text, how can I get the volume and orders value of the row? And another one, if it's possible, how can I make it extract only from a single date, like 2014-05-08?

It's pretty simple but I can't figure it out because I'm quite new to all of that and I don't even know what to google for

Here's what I've tried so far:

=IMPORTXML("http://api.eve-marketdata.com/api/item_history2.xml?char_name=demo&region_ids=10000002&type_ids=16669&days=2" , "/emd/result/rowset/row")

Upvotes: 2

Views: 1640

Answers (1)

helderdarocha
helderdarocha

Reputation: 23637

The XPath expression: 

/emd/result/rowset/row

will select a node-set containing all three rows. If you wish to select a particular row, you should attach a predicate [] containing the row you wish, for example:

/emd/result/rowset/row[2]

will select the second row.

To select an individual attribute from the selected row, you just need to add an extra location step containing the attribute name preceded by the @ sign. You can use expressions such as:

/emd/result/rowset/row[1]/@date
/emd/result/rowset/row[1]/@volume
/emd/result/rowset/row[1]/@orders

to obtain the contents of the date, volume and orders attributes of the first row, for example.

Upvotes: 2

Related Questions