From: esquel@sommarskog.se   
      
   > I need to query out the XML data in a table 's column with XML datatype.   
   > The "select xmldata from xmltbl where ..." statement will return several   
   > rows.  Currently I can get the xmldata if the SELECT statement returns a   
   > single row, but the SELECT statement always returns multiple rows.  My   
   > goal is to get the result set of XML data in all of these rows at one.   
   > Please let me know how I can achieve it.   
   >   
      
   What happens if you run   
      
    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  xmltbl   
    CROSS APPLY xmldata.nodes('/agency') AS Agency(a)   
    CROSS  APPLY Agency.a.nodes('TrafficUnit[TrafficUnit_Number="2"]/Driver') AS   
   Driver(d)   
      
      
   That is, the same thing except the DECLARE statement which you don't need?   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)