From: chris.sjpd@gmail.com   
      
   Hi Erland,   
      
   1. All of these xml files are already in a table.   
      
   2. The data type of the column in the table is XML.  I try the below query but   
   get the error.   
      
      
   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)   
      
   Errors:   
   Msg 512, Level 16, State 1, Line 7   
   Subquery returned more than 1 value. This is not permitted when the subquery   
   follows =, !=, <, <= , >, >= or when the subquery is used as an expression.   
      
   The error makes sense because declaration   
   DECLARE @x xml = (select xmldata from xmltbl where id betwen 1 and 3)   
      
   would return multiple rows & each row is an XML file like this:   
      
   xmldata   
   =========   
      
      
      
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)