代码之家 › 专栏 › 技术社区 › Catalyx

针对Google工作表的IMPORTXML XPath\u查询

xquery google-sheets xpath xml

Catalyx · 技术社区 · 6 年前

我在用GoogleSheet的 IMPORTXML 函数用于检索早一年的每个日历日期的数据,或检索数据可用的最近的一年前日期的数据。

 <entry>
    <id>http://data.treasury.gov/Feed.svc/DailyTreasuryYieldCurveRateData(6794)</id>
    <title type="text"></title>
    <updated>2018-02-06T22:05:38Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="DailyTreasuryYieldCurveRateDatum" href="DailyTreasuryYieldCurveRateData(6794)" />
    <category term="TreasuryDataWarehouseModel.DailyTreasuryYieldCurveRateDatum" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Id m:type="Edm.Int32">6794</d:Id>
        <d:NEW_DATE m:type="Edm.DateTime">2017-02-24T00:00:00</d:NEW_DATE>
        <d:BC_1MONTH m:type="Edm.Double">0.4</d:BC_1MONTH>
        <d:BC_3MONTH m:type="Edm.Double">0.52</d:BC_3MONTH>
        <d:BC_6MONTH m:type="Edm.Double">0.65</d:BC_6MONTH>
        <d:BC_1YEAR m:type="Edm.Double">0.8</d:BC_1YEAR>
        <d:BC_2YEAR m:type="Edm.Double">1.12</d:BC_2YEAR>
        <d:BC_3YEAR m:type="Edm.Double">1.38</d:BC_3YEAR>
        <d:BC_5YEAR m:type="Edm.Double">1.8</d:BC_5YEAR>
        <d:BC_7YEAR m:type="Edm.Double">2.12</d:BC_7YEAR>
        <d:BC_10YEAR m:type="Edm.Double">2.31</d:BC_10YEAR>
        <d:BC_20YEAR m:type="Edm.Double">2.69</d:BC_20YEAR>
        <d:BC_30YEAR m:type="Edm.Double">2.95</d:BC_30YEAR>
        <d:BC_30YEARDISPLAY m:type="Edm.Double">2.95</d:BC_30YEARDISPLAY>
      </m:properties>
    </content>
  </entry>
  <entry>
    <id>http://data.treasury.gov/Feed.svc/DailyTreasuryYieldCurveRateData(6795)</id>
    <title type="text"></title>
    <updated>2018-02-06T22:05:38Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="DailyTreasuryYieldCurveRateDatum" href="DailyTreasuryYieldCurveRateData(6795)" />
    <category term="TreasuryDataWarehouseModel.DailyTreasuryYieldCurveRateDatum" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:Id m:type="Edm.Int32">6795</d:Id>
        <d:NEW_DATE m:type="Edm.DateTime">2017-02-27T00:00:00</d:NEW_DATE>
        <d:BC_1MONTH m:type="Edm.Double">0.44</d:BC_1MONTH>
        <d:BC_3MONTH m:type="Edm.Double">0.5</d:BC_3MONTH>
        <d:BC_6MONTH m:type="Edm.Double">0.68</d:BC_6MONTH>
        <d:BC_1YEAR m:type="Edm.Double">0.81</d:BC_1YEAR>
        <d:BC_2YEAR m:type="Edm.Double">1.2</d:BC_2YEAR>
        <d:BC_3YEAR m:type="Edm.Double">1.46</d:BC_3YEAR>
        <d:BC_5YEAR m:type="Edm.Double">1.87</d:BC_5YEAR>
        <d:BC_7YEAR m:type="Edm.Double">2.18</d:BC_7YEAR>
        <d:BC_10YEAR m:type="Edm.Double">2.36</d:BC_10YEAR>
        <d:BC_20YEAR m:type="Edm.Double">2.72</d:BC_20YEAR>
        <d:BC_30YEAR m:type="Edm.Double">2.98</d:BC_30YEAR>
        <d:BC_30YEARDISPLAY m:type="Edm.Double">2.98</d:BC_30YEARDISPLAY>
      </m:properties>
    </content>
  </entry>
  <entry>

这是我当前用于检索2017年2月27日数据的XPath查询:

//*[local-name() = 'NEW_DATE'][text() = '2017-02-27T00:00:00']/..

这是显示以下内容的结果:

6795    2017-02-27T00:00:00 0.44    0.5 0.68    0.81    1.2 1.46    1.87    2.18    2.36    2.72    2.98    2.98

有没有办法:

检索除“d:Id”元素(“6795”以上)外显示的所有数据和
如果搜索没有数据的日期(例如2017年 2月25日将导致“不适用”错误,因为查询不会返回任何缺少的日期),查询将默认为下一个可用的转发日期(例如2017年2月27日)?

我避免使用IF函数来减少 导入XML 电话。

2 回复 | 直到 6 年前

Tanaike 6 年前

这个答案怎么样?

关于你的第一个问题

示例:

=TRANSPOSE(IMPORTXML(A1, "//*[local-name() = 'NEW_DATE'][text() = '2017-02-27T00:00:00']/../*[local-name()!='Id']"))

“A1”是的URL http://data.treasury.gov/feed.svc/DailyTreasuryYieldCurveRateData?$filter=year(NEW_DATE)%20eq%202017 .
对于 //*[local-name() = 'NEW_DATE'][text() = '2017-02-27T00:00:00']/.. ,值,除 <d:Id m:type="Edm.Int32">6795</d:Id> 已检索。
结果被转置,因为值被输出到行。

结果:

关于你的第二个问题

什么时候 =TRANSPOSE(IMPORTXML(A1, "//*[local-name() = 'NEW_DATE'][text() = '2017-02-25T00:00:00']/../*[local-name()!='Id']")) 是使用的,结果是 #N/A 已检索。

如果我误解了你的问题,我很抱歉。

Catalyx 6 年前

因为Google Sheets(GS)似乎只与XPath 1.0(其 documentation 和产品论坛页面 here 和 here 不确认或澄清截至目前支持的版本),XPath 2.0等替代方法 IF-THEN-ELSE 声明 cannot be used . 相反,可以使用GS本机函数过滤所查找的源XML数据。

问题1

根据@Tanaike提出的解决方案,可以使用XPath“not”命令来检索元素的所有子节点,保存一个。 ! ,如下应用于“NEW\u DATE”的父元素,并省略子元素“Id”。 TRANSPOSE 用于以柱状形式显示。(A1是包含问题中的源XML URL的单元格。)

=TRANSPOSE(IMPORTXML(A1, "//*[local-name() = 'NEW_DATE'][text() = '2017-02-25T00:00:00']/../*[local-name()!='Id']"))

问题2

要搜索一年前的特定日期,如果该日期的数据丢失,要检索最近的转发日期,需要嵌套的GS函数首先使用上述公式检索“NEW\u date”数据,然后使用 SORT 和至 MATCH 最接近的可用日期。这个 INDEX 然后在反向排序元素上使用函数来选择适当的日期。这个 CONCATENATE , TEXT 和 TODAY 函数仅用于以与XML数据兼容的格式设置一年前的日期。公式如下。

=TRANSPOSE(IMPORTXML(A1,CONCATENATE("//*[local-name() = 'NEW_DATE'][text() = '",INDEX(SORT(IMPORTXML(A1, "//*[local-name() = 'NEW_DATE']"),1,FALSE),MATCH(CONCATENATE(TEXT(TODAY()-365,"YYYY-MM-DD"),"T00:00:00"),SORT(IMPORTXML(A1, "//*[local-name() = 'NEW_DATE']"),1,FALSE),-1)),"']/../*[local-name()! = 'Id']")))