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)