From: esquel@sommarskog.se   
      
   chris (chris.sjpd@gmail.com) writes:   
   > DECLARE @x xml = (select xmldata from xmltbl where id betwen 1 and 3)   
   > SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]',   
   'varchar(20)') AS [AccidentReportNumber],   
   >        Agency.a.value('(NumberInjured-AllPages/text())[1]', 'varchar(20)')   
   AS [NumberInjured],   
   >        Driver.d.value('(AddressFullText/text())[1]','nvarchar(60)') AS   
   AddressFullText   
   > FROM   @x.nodes('/agency') AS Agency(a)   
   > CROSS  APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS   
   Driver(d)   
   >   
      
   So rather than   
      
   > FROM   @x.nodes('/agency') AS Agency(a)   
      
      
   Try   
      
   FROM  xmltbl   
   CROSS APPLY xmldata.nodes('/agency') AS Agency(a)   
      
   And then the second CROSS APPLY after that.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)