CODEWITHSUNDEEP
xmlgoogle-sheetsxml-parsinggoogle-sheets-formula
Bricky
Bricky

Reputation: 3

IMPORTXML returns blank results

I am trying to import data from https://www67.myfantasyleague.com/2019/export?TYPE=liveScoring&L=64741&APIKEY=&W=1&DETAILS=1 into a Google Sheet to parse. Specially I want to return each franchise id and which players are subchildren.

I tried two formulas in the google sheet and can't figure out why I'm not getting any results back. No errors, just a blank cell.

=IMPORTXML("https://www67.myfantasyleague.com/2019/export?TYPE=liveScoring&L=64741&APIKEY=&W=1&DETAILS=1&JSON=0","/"

=IMPORTXML("https://www67.myfantasyleague.com/2019/export?TYPE=liveScoring&L=64741&APIKEY=&W=1&DETAILS=1","//franchise[@id='0006']//players")

Link to Google Sheet

What am I doing wrong here? I can get results if I use IMPORTDATA, but I need the structure the XML provides to make queries correctly. Any help is greatly appreciated!

Here is a snippit of the XML where I want all the player ids under each franchise id (player ids can be under multiple franchise ids, fyi)

<liveScoring week="1">
<matchup>
<franchise id="0006" score="109.52" gameSecondsRemaining="0" playersYetToPlay="0" playersCurrentlyPlaying="0" isHome="0">
<players>
<player id="7393" score="25.30" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="14056" score="21.62" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="11672" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="11644" score="14.56" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="12634" score="7.50" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="11671" score="4.80" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="10261" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="9662" score="16.38" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="13722" score="14.40" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="13590" score="9.40" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="13633" score="10.40" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="9988" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="13678" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
</players>
</franchise>
<franchise id="0005" score="163.78" gameSecondsRemaining="0" playersYetToPlay="0" playersCurrentlyPlaying="0" isHome="1">
<players>
<player id="12184" score="24.30" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="11644" score="14.56" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="11227" score="12.70" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="10261" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="13636" score="5.50" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="9427" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="12154" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="10273" score="5.36" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="13606" score="10.30" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="13138" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="13968" score="0.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="9431" score="27.60" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="12197" score="3.00" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="12157" score="13.00" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="13130" score="42.90" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="12263" score="17.50" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="11192" score="23.20" gameSecondsRemaining="0" status="starter" updatedStats=""/>
<player id="11660" score="4.10" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="11677" score="3.70" gameSecondsRemaining="0" status="nonstarter" updatedStats=""/>
<player id="11257" score="7.00" gameSecondsRemaining="0" status="starter" updatedStats=""/>
</players>
</franchise>
</matchup>

Upvotes: 0

Views: 85

Answers (1)

Tanaike
Tanaike

Reputation: 201643

  • You want to retrieve "player ID" using "franchise ID".
  • You want to achieve this using IMPORTXML.
  • You want to know that the values from "/" and "//franchise[@id='0006']//players" are the empty.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • When I saw the XML data, the text is not set. By this, the xpaths of "/" and "//franchise[@id='0006']//players" return the empty.
    • In your case, it is required to retrieve the values of IDs using the attribute.

Modified formulas:

For the following formulas, the URL of https://www67.myfantasyleague.com/2019/export?TYPE=liveScoring&L=64741&APIKEY=&W=1&DETAILS=1 is put in the cell A1.

Patten 1:

When you want to retrieve all IDs, how about ths following formula?

=IMPORTXML(A1,"//@id")

Patten 2:

When you want to retrieve the player's IDs of the franchise ID of "0006", how about ths following formula?

=IMPORTXML(A1,"//franchise[@id='0006']//player/@id")
Result:

enter image description here

Reference:

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

Upvotes: 1

Related Questions