From: esquel@sommarskog.se   
      
   chris (chris.sjpd@gmail.com) writes:   
   > When I run the below query, it has no errors but returns no data. Could   
   > you please take a look at my query & let me know what I'm missing?   
   > Appreciate your helps very much.   
   >   
   > SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]',   
   'varchar(20)') AS [AccidentReportNumber],   
   >        Vehicle.v.value('(Make/text())[1]', 'varchar(20)') AS [Make],   
   >      Myrole.r.value('(TrafficUnitDriverCheck/text())[1]', 'nvarchar(10)') AS   
   [Driver],   
   >      Myrole.r.value('(SobrietyDrugPhysicalA/text())[1]', 'varchar(20)') AS   
   [Sobriety_Drug_Physical_A]   
   >   
   >  FROM xmltbl   
   >  CROSS APPLY xmlcontent.nodes('/agency') AS Agency(a)   
   >  CROSS  APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver')   
   AS Driver(d)   
   >  CROSS APPLY Agency.a.nodes('TrafficUnit/TrafficUnit_Number/Driver/Vehicle')   
   AS Vehicle(v)   
   >  CROSS APPLY Agency.a.nodes('/TrafficUnit') AS Myrole(r)   
   >   
      
   What I can see directly is that you are not missing anything, but you   
   are having one too many. This line:   
      
   >  CROSS APPLY Agency.a.nodes('/TrafficUnit') AS Myrole(r)   
      
   Should be   
      
     CROSS APPLY Agency.a.nodes('TrafficUnit') AS Myrole(r)   
      
   I have learnt the hard way that if you have a leading slash in call to nodes   
   in CROSS APPLY, you don't get anything back.   
      
   A general tip with this query is to change CROSS to OUTER for one or more   
   until you get row back. With OUTER APPLY the rows in the left table are   
   retained. This way, you can figure out which of the CROSS APPLY that   
   is giving you problems.   
      
   Then you will have to take it from there. If I look at your post from   
   June 11th, it seems that the structure is:   
      
       
       1   
          
          ...   
          
          
          ...   
          
      
   But the second CROSS APPLY is written for   
      
      
       
          
             
                
              ...   
                
             
          
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)