How to extract specific attribute values using IMPORTXML in Google Sheets

Other Questions Google docs
1

I’m struggling with the correct IMPORTXML formula to extract data from an XML feed. I need to get a specific quantity value from this structure:

<?xml version="1.0" encoding="utf-8"?>
<gameapi version="2">
    <timestamp>2017-01-02 11:59:29</timestamp>
    <data>
        <status>4</status>
        <statusTime>2017-01-02 12:13:29</statusTime>
        <lastOnline>2016-12-16 03:13:10</lastOnline>
        <configuration>
            <permissions>3</permissions>
            <deployment>0</deployment>
            <corporateAccess>1</corporateAccess>
            <allianceAccess>1</allianceAccess>
        </configuration>
        <battleSettings>
            <standingsSource playerID="99005805"/>
            <standingThreshold level="0"/>
            <statusAction active="0" level="0"/>
            <aggressionResponse active="0"/>
            <warResponse active="1"/>
        </battleSettings>
        <resources name="materials" key="itemID" columns="itemID,amount">
            <item itemID="16275" amount="9300"/>
            <item itemID="4051" amount="12140"/>
        </resources>
    </data>
    <expires>2017-01-02 12:37:11</expires>
</gameapi>

I want to pull the amount value (12140) from the item where itemID equals “4051”. I’ve attempted several xpath expressions but none work correctly. What’s the proper syntax for this specific extraction?

2

Use this xpath: //resources[@name='materials']/item[@itemID='4051']/@amount. It’s more specific than targeting any item element - it goes to the resources node with name ‘materials’ first, then filters for the itemID. Your full formula: =IMPORTXML("your_xml_url", "//resources[@name='materials']/item[@itemID='4051']/@amount"). I’ve found this targeted approach prevents issues when XML structures have similar elements at different levels. The key is being explicit about the parent container instead of using a global search, which can return unexpected results or fail completely depending on how Google Sheets processes the XML.

3

You’ll want to use //item[@itemID='4051']/@amount as your xpath expression. This targets the item with itemID ‘4051’ and grabs its amount attribute value. Your full IMPORTXML formula becomes =IMPORTXML("your_xml_url", "//item[@itemID='4051']/@amount"). The double slash searches anywhere in the document, square brackets filter by attribute, and @ symbols reference both the filter and target attributes. I’ve used this pattern tons of times with XML APIs and it’s rock solid with Google Sheets IMPORTXML.

4

Try this xpath: //resources/item[@itemID="4051"]/@amount - single quotes sometimes break in importxml so use double quotes instead. Also check that your XML URL is actually accessible - I’ve run into feeds that need specific headers which importxml can’t handle.