ok. so if they do repeat I really need to know how many times they repeat and somehow dynamically construct the statement? Otherwise it looks like hardcoding the number of repeats for static record is the way to go.
arthurolcot (9/28/2012)
If both GPSFixes and GPSFix repeat then you'll need to have 2 x .nodes methods(). Here is an example using your xml as a base (i have stripped it back a lot purely to make it easier to read)
DECLARE @xml XML
SET @xml = '
<VehicleInfoMessage>
<GPSFixes NumFixes="1">
<GPSFix>
<FixTime>2012-09-28 13:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
</GPSFix>
<GPSFix>
<FixTime>2011-08-28 12:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
</GPSFix>
</GPSFixes>
<GPSFixes NumFixes="2">
<GPSFix>
<FixTime>2010-07-28 11:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
</GPSFix>
<GPSFix>
<FixTime>2009-06-28 10:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
</GPSFix>
</GPSFixes>
</VehicleInfoMessage>'
SELECT t.c.value('(@NumFixes)[1]', 'smallint') AS 'NumFixes'
, u.c.value('(FixTime/text())[1]', 'varchar(20)') AS 'FixTime'
, u.c.value('(FixTimeUTF/text())[1]', 'varchar(20)') AS 'FixTimeUTF'
FROM @xml.nodes('/VehicleInfoMessage/GPSFixes') T(c)
CROSS APPLY t.c.nodes('GPSFix') u(c)