Extract sub category info from XML using Google Sh

2019-08-18 03:35发布

问题:

I am trying to capture elements of an qmd file (that is xml markup) using Google Sheets importxml. Based on the reply to my question Google ImportXML from QGIS metadata file I am able to capture the primary tag.

=transpose(IMPORTXML("https://drive.google.com/uc?id=1AI2C8hQnSOuuoyJXizYBszGmpMXW8xxT&export=download","//authid"))

However I can't access some elements

    <spatialrefsys>
      <proj4>+proj=utm +zone=55 +south +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs</proj4>
      <srsid>2449</srsid>
      <srid>28355</srid>
      <authid>EPSG:28355</authid>
      <description>GDA94 / MGA zone 55</description>
      <projectionacronym>utm</projectionacronym>
      <ellipsoidacronym>GRS80</ellipsoidacronym>
      <geographicflag>false</geographicflag>
    </spatialrefsys>
  </crs>
  <extent>
    <spatial minx="322783.17999999999301508" minz="0" crs="EPSG:28355" maxx="337384.35999999998603016" miny="8170597.66000000014901161" maxz="0" dimensions="2" maxy="8181833.33999999985098839"/>
    <temporal>
      <period>
        <start></start>
        <end></end>
      </period>
    </temporal>
  </extent>

From the //extent I want minx, maxx

for XPath I have tried "//extent", "//extent/spatial", "//extent/spatial[@*]" and "@minx"

but get no result.

回答1:

  • You want to retrieve the values of maxx and minx using IMPORTXML().

If my understanding is correct, how about this modification?

Modified formula:

=TRANSPOSE(IMPORTXML(A1,"//spatial/@maxx | //spatial/@minx"))
  • https://drive.google.com/uc?id=1AI2C8hQnSOuuoyJXizYBszGmpMXW8xxT&export=download is put in the cell "A1".
  • In this case, //spatial/@maxx | //spatial/@minx is used as the xpath.
    • Attibute values can be retrieved with @.
  • When you want to use these values as the number, you can use this.
  • If you want to use the values as the text, for example, how about the following formula?

    =TRANSPOSE(ARRAYFORMULA(TEXT(IMPORTXML(A1,"//spatial/@maxx | //spatial/@minx"),"0.0000000000")))
    

Result:

Reference:

  • IMPORTXML

If I misunderstood your question and this was not the result you want, I apologize.