xml - VBA DOM Variable in XPath Attribute -

i have program searches , gathers (via array) specific author , 'booktype' , 'booktitle' trying learn how use author's name - i've stored in array - variable in xpath 'store location'. like: "/catalog/book/misc/publisherauthor id="myvar"/storelocation"

three things confusing me: 1. how declare variable in xpath if it's array (if possible)? 2. bad idea declare statement inside loop? 3. xpath/dom logic correct?

<?xml version="1.0"?> <catalog> <book id="adventure">    <author>gambardella, matthew</author>    <title>xml developer's guide</title>    <price>44.95</price>    <misc>         <publisher id="5691">             <publisherlocation>los angeles</publisherlocation>         </publisher>         <publishedauthor id="gambardella, matthew">             <storelocation>store b</storelocation>         </publishedauthor>     </misc> </book> <book id="adventure">    <author>ralls, kim</author>    <title>midnight rain</title>    <price>5.95</price>    <misc>         <publisher id="4787">             <publisherlocation>new york</publisherlocation>         </publisher>         <publishedauthor id="ralls, kim">             <storelocation>store b</storelocation>         </publishedauthor>     </misc> </book> <book id="adventure">    <author>boal, john</author>    <title>mist</title>    <price>15.95</price>    <misc>         <publisher id="8101">             <publisherlocation>new mexico</publisherlocation>         </publisher>         <publishedauthor id="boal, john">             <storelocation>store b</storelocation>         </publishedauthor>     </misc> </book> <book id="mystery">    <author>ralls, kim</author>    <title>some mystery book</title>    <price>9.95</price>    <misc>         <publisher id="6642">             <publisherlocation>new york</publisherlocation>         </publisher>         <publishedauthor id="ralls, kim">             <storelocation>store b</storelocation>         </publishedauthor>     </misc> </book> </catalog> 

my code:

option explicit  sub mysub()  dim xmlfile variant dim author variant dim athr string, booktype string, title string, storelocation string dim authorarray() string, booktypearray() string, titlearray() string, storelocationarray() string dim long, x long, j long  dim mainworkbook workbook dim n ixmldomnode set mainworkbook = activeworkbook set xmlfile = createobject("microsoft.xmldom") xmlfile.load ("c:\books.xml")   x = 1 j = 0  set author = xmlfile.selectnodes("/catalog/book/author") = 0 (author.length - 1)     redim preserve authorarray(0 i)     redim preserve booktypearray(0 i)     redim preserve titlearray(0 i)     redim preserve storelocationarray(0 i)      athr = author(i).text     booktype = author(i).parentnode.getattribute("id")     title = author(i).parentnode.getelementsbytagname("title").item(0).nodetypedvalue     storelocation = author(i).parentnode.selectsinglenode("/misc/publishedauthor[@id=""&athr(j)&""]/storelocation").nodevalue      if athr = "ralls, kim"          authorarray(j) = athr         booktypearray(j) = booktype         titlearray(j) = title         storelocationarray(j) = storelocation          j = j + 1         x = x + 1     end if next  range("a3:a" & ubound(authorarray) + 1) = worksheetfunction.transpose(authorarray) range("b3:b" & ubound(booktypearray) + 1) = worksheetfunction.transpose(booktypearray) range("c3:c" & ubound(booktypearray) + 1) = worksheetfunction.transpose(titlearray) range("d3:d" & ubound(booktypearray) + 1) = worksheetfunction.transpose(titlearray) 

to specific, it's line don't understand how setup:

storelocation = author(i).parentnode.selectsinglenode("/misc/publishedauthor[@id=""&athr(j)&""]/storelocation").nodevalue 

is doing frowned upon? there more logical way?

thank guidance, help, or comments :)

you need double-up quotes escape them:

storelocation = author(i).parentnode.selectsinglenode( _    "/misc/publishedauthor[@id=""" & athr & """]/storelocation").nodevalue 

edit: worked me

sub mysub()  dim xmlfile variant dim author variant dim athr string, booktype string, title string, storelocation string dim authorarray() string, booktypearray() string dim titlearray() string, storelocationarray() string dim long, x long, j long, locs  dim mainworkbook workbook dim n ixmldomnode set mainworkbook = activeworkbook set xmlfile = createobject("microsoft.xmldom") xmlfile.load ("c:\books.xml")  x = 1 j = 0  set author = xmlfile.selectnodes("/catalog/book/author")  = 0 (author.length - 1)      athr = author(i).text      'moved bunch of code inside check...     if athr = "ralls, kim"          booktype = author(i).parentnode.getattribute("id")         title = author(i).parentnode.getelementsbytagname("title").item(0).nodetypedvalue          set locs = author(i).parentnode.selectnodes( _                "misc/publishedauthor[@id='" & athr & "']/storelocation")          if locs.length > 0             storelocation = locs(0).nodetypedvalue         else             storelocation = "???"         end if          redim preserve authorarray(0 j)         redim preserve booktypearray(0 j)         redim preserve titlearray(0 j)         redim preserve storelocationarray(0 j)          authorarray(j) = athr         booktypearray(j) = booktype         titlearray(j) = title         storelocationarray(j) = storelocation          j = j + 1         x = x + 1      end if next  if j > 0      range("a3").resize(j, 1).value = worksheetfunction.transpose(authorarray)     range("b3").resize(j, 1).value = worksheetfunction.transpose(booktypearray)     range("c3").resize(j, 1).value = worksheetfunction.transpose(titlearray)     range("d3").resize(j, 1).value = worksheetfunction.transpose(storelocationarray)  end if  end sub