5

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="[email protected]" 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
WorkflowCreationPath:SW|d6bda0a0-646d-4e70-ba11-da9956f380c2,19;"/>
3
  • 1
    I'm having a bit of difficulty visualising your dsQueryResponse. Could you mock up an xml-style one?
    – James Love
    May 13, 2011 at 16:12
  • 2
    Did you use the phrase, In simple SQL terms, it be.... because you are literally a pirate? May 13, 2011 at 16:25
  • @Abe ha, no, that was a typo. @James, I'll get an image up and see if that helps. May 13, 2011 at 16:28

2 Answers 2

4

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.

UPDATE:

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

<dsQueryResponse>
 <Labs>
  <Rows>
    <Row>
      <LabID>1</LabID>
      <DataOwner>Abe Miessler</DataOwner>
    </Row>
  </Rows>
 </Labs>
 <Requests>
  <Rows>
   <LabID>1</LabID>
   <PrinterModel>blah</PrinterModel>
  </Rows>
  <Rows>
   <LabID>1</LabID>
   <PrinterModel>blah 2</PrinterModel>
  </Rows>
  <Rows>
   <LabID>2</LabID>
   <PrinterModel>blah3333</PrinterModel>
  </Rows>
 </Requests>
</dsQueryResponse>
8
  • 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. May 13, 2011 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. May 13, 2011 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. May 13, 2011 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? May 13, 2011 at 18:19
  • Yep, I was replacing you with me, and yes, that looks like the output I'm seeing. May 13, 2011 at 18:48
1

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.

2
  • No problem! I am a little suprised by all the substring action you have there. Do you know why that is needed? May 19, 2011 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. May 19, 2011 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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