I'm working on a DVWP where I'm trying to aggregate some data from a list based off some information in another list. I'm struggling to come up with the right XPath expression to do what I want.

In simple SQL terms, it be select * from requests where labid in (select labid from labs where dataowner = currentuser)

My dsQueryResponse looks like this:

dsQueryResponse -
Labs - Rows - Row - LabId, Data Owner
Requests - Rows -LabID, other fields
SPD ds Response

What would the XPath filter look like for that? I'm struggling trying to get the collection of labs from the Lab list where the data owner would be the current user, then using those lab ids to return all the requests from those Labs.

The Lab dsQueryResponse looks like this (sanitized some). Requests is in similar output.

<Row Title="1" LabID="1" Room="none" Lab_x0020_Location="Warehouse" Lab_x0020_Contact="Pirate Eric" Lab_x0020_Phone="555-9543" Lab_x0020_Hours="[M-F] 8-5|Sa-Su Closed" LabStatsID="" Lab_x0020_Status="Inactive" 
    ContentType="Computer Lab" Data_x0020_Owner="Alexander, Eric" NewLabVe="" UpdateLa="5" Building="No Physical Location" LabType="N" SpecialNeeds="N" OldLabID="" College="Academic Support" Department="Academic Support" 
    Department_x0020_Head="Our Boss" Department_x0020_Head_x0020_Emai="user@place.com" ID="1" Modified="2011-04-25T13:19:28Z" Created="2010-09-28T17:53:49Z" Author="Alexander, Eric" 
    Editor="Alexander, Eric" _UIVersionString="1.0" Attachments="0" LinkTitleNoMenu="1" LinkTitle="1" FileRef="" FileDirRef="" FSObjType="0" ProgId="" ScopeId="{68FB6470-C1B0-419E-B668-FC4FB7E3C4CF}" DocIcon="" MetaInfo="vti_encoding:SR|utf8-nl
  • 1
    I'm having a bit of difficulty visualising your dsQueryResponse. Could you mock up an xml-style one? – James Love May 13 '11 at 16:12
  • 2
    Did you use the phrase, In simple SQL terms, it be.... because you are literally a pirate? – Abe Miessler May 13 '11 at 16:25
  • @Abe ha, no, that was a typo. @James, I'll get an image up and see if that helps. – Eric Alexander May 13 '11 at 16:28

If your XML is structured like your file structure try this:

/dsQueryResponse/Requests/Rows[ LabID=/dsQueryResponse/Labs/Rows/Row[DataOwner='Abe Miessler']/LabID ]

I am assuming that you have a LabID under Rows that was just left out of the screen shot.


I tested it with the following XML and it appeared to work:

      <DataOwner>Abe Miessler</DataOwner>
   <PrinterModel>blah 2</PrinterModel>
  • Yes, there would be a LabID column, there were just too many to list and it's further down, I wanted to give a picture of what SPD was returning. I'll give this a whirl and see how it goes. – Eric Alexander May 13 '11 at 16:56
  • This doesnt seem to be working, is there something I can do to output the dsQueryResponse so I can figure this out better? I'm not sure if XPath likes it how a data owner can have more than 1 lab. – Eric Alexander May 13 '11 at 17:57
  • I'm not sure how to output the dsQueryResponse. Just to double check, you did replace the portion that says [DataOwner='Abe Miessler'] right? Try it with a hard coded value that you know exists first. – Abe Miessler May 13 '11 at 18:11
  • Also, I was looking at this link and the XML they have doesn't seem the same as yours. msdn.microsoft.com/en-us/library/ff602042.aspx Does that look like what you are dealing with? – Abe Miessler May 13 '11 at 18:19
  • Yep, I was replacing you with me, and yes, that looks like the output I'm seeing. – Eric Alexander May 13 '11 at 18:48

After a lot of trial and error and a lot of help from Marc Anderson, we finally got this sorted out. The actual Rows expression I needed was,

<xsl:variable name="Rows" select="/dsQueryResponse/Labs/Rows/Row[substring-after(substring-before(substring-after(@Data_x0020_Owner, 'ID='), '&lt;'), '&gt;') = $UserID]" />

I'm returning a filtered list of labs the current user owns, then using a joined subview to show all the associated requests for that lab. Thanks for the help Abe, I appreciate it.

  • No problem! I am a little suprised by all the substring action you have there. Do you know why that is needed? – Abe Miessler May 19 '11 at 14:04
  • 1
    Because the $UserID was passing through the users site id. Typically it returns lastname, fristname I thought. Since my @Data_x0020_Owner column was returning a ln, fn format, I had to do all sorts of substrings to get the userid out of that HTML mess so that it would properly join and bring back the proper data. Messy. – Eric Alexander May 19 '11 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.