From: esquel@sommarskog.se   
      
   chris H (chris.sjpd@gmail.com) writes:   
   > This is all I have to work with :-(  The xml file is coming from an   
   > application which combines some columns from db & others from the xml   
   > form.  I have spent a lot of time to get it work because we only need   
   > data in a few columns   
      
   What you post now is something completely different. That is a complete   
   XML document, and indeed it is possible to get data out of it. However,   
   since it is so big, and has so many tags, it takes a while to get all   
   you need. Here is a start:   
      
   SELECT Agency.a.value('(CollisionOccurredDate-AllPages/text())[1]',   
                  'varchar(20)') AS [CollisionOccurredDate-AllPage],   
          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)   
      
   You may note that there is no OPENXML. OPENXML was introduced in SQL 2000.   
   With SQL 2005 we got XQuery, which is generally considered better to   
   get data out from XML.   
      
   The nodees function gives you nodes in the document, and the first is   
   the root node, and the CollisionOccurredDate is directly below the   
   root node.   
      
   The AddressFullText node on the other hand, is inside the Driver node which   
   is inside the TrafficUnit node wihch is under the root node. On top of   
   all there is more than one TrafficUnit node, and it appears you want   
   the second.   
      
   The value function extracts the value a singlee element. The /text() thing   
   is not needed, but tends to be more efficient.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)