Hi. To strip out the DTD you can use the CONVERT function e.g.:
DECLARE @xmlVarchar VARCHAR(MAX)
SET @xmlVarchar = '<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE VehicleInfo SYSTEM "http://www.vehicleinfo.com/dconnect/DConnect.v2.0.dtd">
<VehicleInfoMessage Type="GPS">
<VIN>4444TCFEXB8543456</VIN>
<FleetId>421250</FleetId>
<MsgId>415583965696</MsgId>
<MessageTime>2012-09-28 13:27:16 GMT</MessageTime>
<MessageTimeUTF>1348838836</MessageTimeUTF>
<DeliveryStatus>Current</DeliveryStatus>
<GPSFixes NumFixes="1">
<GPSFix>
<FixTime>2012-09-28 13:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
<Latitude>434.09230</Latitude>
<Longitude>-370.21554</Longitude>
<Ignition>On</Ignition>
<Speed Type="Avg" Units="MPH">9</Speed>
<Speed Type="Inst" Units="MPH">16</Speed>
<Speed Type="Max" Units="MPH">16</Speed>
<Heading>250</Heading>
<Odometer>27933.495</Odometer>
<AgeInMiles>0.006</AgeInMiles>
</GPSFix>
</GPSFixes>
</VehicleInfoMessage>'
SELECT CONVERT(XML, @xmlVarchar, 2)
This gets rid of the DTD for you nicely.
The .nodes method is useful if you have repeating sections in the XML and you need to shred it into a relational table. Looking at your example it looks like the section GPSFixes could repeat? If so, then .nodes is your best bet for getting that xml into a relational table format.
Here is an example of the usage
DECLARE @xmlVarchar VARCHAR(MAX)
SET @xmlVarchar = '<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE VehicleInfo SYSTEM "http://www.vehicleinfo.com/dconnect/DConnect.v2.0.dtd">
<VehicleInfoMessage Type="GPS">
<VIN>4444TCFEXB8543456</VIN>
<FleetId>421250</FleetId>
<MsgId>415583965696</MsgId>
<MessageTime>2012-09-28 13:27:16 GMT</MessageTime>
<MessageTimeUTF>1348838836</MessageTimeUTF>
<DeliveryStatus>Current</DeliveryStatus>
<GPSFixes NumFixes="1">
<GPSFix>
<FixTime>2012-09-28 13:27:10 GMT</FixTime>
<FixTimeUTF>1348838830</FixTimeUTF>
<Latitude>434.09230</Latitude>
<Longitude>-370.21554</Longitude>
<Ignition>On</Ignition>
<Speed Type="Avg" Units="MPH">9</Speed>
<Speed Type="Inst" Units="MPH">16</Speed>
<Speed Type="Max" Units="MPH">16</Speed>
<Heading>250</Heading>
<Odometer>27933.495</Odometer>
<AgeInMiles>0.006</AgeInMiles>
</GPSFix>
</GPSFixes>
</VehicleInfoMessage>'
DECLARE @xml XML
SELECT @xml = CONVERT(XML, @xmlVarchar, 2)
SELECT @xml.value('(/VehicleInfoMessage/@Type)[1]', 'varchar(10)') AS 'Type'
, @xml.value('(/VehicleInfoMessage/VIN)[1]', 'varchar(20)') AS 'Vin'
, t.c.value('(FixTime)[1]', 'varchar(20)') AS 'FixTime'
, t.c.value('(FixTimeUTF)[1]', 'varchar(20)') AS 'FixTimeUTF'
FROM @xml.nodes('/VehicleInfoMessage/GPSFixes/GPSFix') T(c)