From: chris.sjpd@gmail.com   
      
   Hi Erland,   
      
   Thank you so much for your help.  I'm able to query out some data now.  Do you   
   know the best way to query multiple xml files?  All of the files have the same   
   file format, except the value.   
      
   Here is my current code which only works for 1 single xml file.   
      
   declare @x xml = (select xmldata from xmltest where  DateCreated =   
   '2020-06-10' )   
      
   SELECT Agency.a.value('(AccidentReportNumber-AllPages/text())[1]',   
   'varchar(20)') AS [AccidentReportNumber],   
          Agency.a.value('(CollisionOccurredDate-AllPages/text())[1]',   
   'varchar(20)') AS [CollisionOccurredDate],   
          Agency.a.value('(Time-AllPages/text())[1]', 'varchar(20)') AS [Time],   
   	   Agency.a.value('(MotorVehicleInvolvedWithI-AllPages/text())[1]',   
   'varchar(20)') AS [MotorVehicleInvolvedWithI],   
   	   Agency.a.value('(PedestrianActionA-AllPages/text())[1]', 'varchar(20)') AS   
   [PedestrianAction],   
   	   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)   
      
   I really appreciate your inputs,   
   Chris   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)