Importing Web XML data

[MrLookout9]

MrExcel has never let me down before...
The vision: I keep a list of all the movies I've seen. I'd like to import some data about each movie.

I have found several sources that provide data in a web XML format. For instance, http://www.omdbapi.com/?s=American History X&r=XML.

What I’d like to do is grab the imdbID for each movie in my list. All movies are in column A. I’d like to have the imdbID in column B.

I am open to other approaches/ data sources.

Thank you!

 

[kocir]

Thank you a lot, you helped me.

 

[kocir]

BTW. It seems that Excel 2007 does not know FILTERXML. How can I solve it?

 

[sergioMabres]

BTW. It seems that Excel 2007 does not know FILTERXML. How can I solve it?

Will get you the value u r looking 4
This is only a sample so u see what functions to use in yr en devour of repalcing FILTERXML
Cheers
Sergio<tag><tag></tag></tag>

 

[kocir]

Can you pls "translate" this formula to your solution?

=FILTERXML($C2;"//div[@id='offers']//div[@id='"&L$1&"']");"neprodává"
and
=(FILTERXML($C2;"//div[@id='minpriceshop']")
and
(FILTERXML($C2;"//div[@id='minprice']")

Thank you.

 

[sergioMabres]

I would need to see what the XML looks like to translate the formula
Sergio

 

[kocir]

How can I upload a txt file here?

 

[kocir]

I would need to see what the XML looks like to translate the formula
Sergio

I have sent you via PM the file.

 

[kocir]

Can you pls "translate" this formula to your solution?

=FILTERXML($C2;"//div[@id='offers']//div[@id='"&L$1&"']");"neprodává"
and
=(FILTERXML($C2;"//div[@id='minpriceshop']")
and
(FILTERXML($C2;"//div[@id='minprice']")

Thank you.

EXPLANATION:
$C2 - contains HTML string (see bellow)
&L$1& - contains name of eshop (for example "MALL.CZ"


HTML data:

 

[sergioMabres]

Can you pls "translate" this formula to your solution?

=FILTERXML($C2;"//div[@id='offers']//div[@id='"&L$1&"']");"neprodává"
and
=(FILTERXML($C2;"//div[@id='minpriceshop']")
and
(FILTERXML($C2;"//div[@id='minprice']")

Thank you.

For =(FILTERXML($C2;"//div[@id='minpriceshop']")

=MID($C2,FIND("id=""minpriceshop",$C2,1)+18,FIND("here is div see image",$C2,1)-FIND("id=""minpriceshop",$C2,1)-18)

For =(FILTERXML($C2;"//div[@id='minprice']")

=MID($C2,FIND("id=""minprice",$C2,1)+14,FIND("here is div seeimage",$C2,1)-FIND("id=""minprice",$C2,1)-14)

For = FILTERXML($C2;"//div[@id='offers']//div[@id='"&L$1&"']");"neprodává"
I think yr formula is incomplete and for sure much much complex

Cheers
Sergio

 

[kocir]

You are right

=IF($G43<>"";IFERROR(FILTERXML($C43;"//div[@id='offers']//div[@id='"&L$1&"']");"neprodává");"")