Re: Loading records from XML document

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thank you, it works now.

The <Track> start tag was missing because when I copied and pasted part of
the file I omitted that line (I'm actually doing a bulk load from a file). I
finally understand (thanks to your help) why my version wasn't working. I
was missing the 2nd "crsns:" for the first two fields (after the slash):

I was doing this:

WITH
(LatitudeDegrees float 'crsns:Position/LatitudeDegrees',
LongitudeDegrees float 'crsns:Position/LongitudeDegrees',
DistanceMeters float 'crsns:DistanceMeters',
AltitudeMeters float 'crsns:AltitudeMeters')

instead of this:

WITH
(LatitudeDegrees float 'crsns:Position/crsns:LatitudeDegrees',
LongitudeDegrees float 'crsns:Position/crsns:LongitudeDegrees',
DistanceMeters float 'crsns:DistanceMeters',
AltitudeMeters float 'crsns:AltitudeMeters')

--


Alain Quesnel
alain@xxxxxxxxxxxx

www.logiquel.com


"Martin Honnen" <mahotrash@xxxxxxxx> wrote in message
news:en%23VisLiHHA.872@xxxxxxxxxxxxxxxxxxxxxxx
Alain Quesnel wrote:

This is what the xml file looks like:

<?xml version="1.0" encoding="utf-8"?>
<TrainingCenterDatabase
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v1";>
<Courses>
<CourseFolder Name="Courses">
<Course>
<Name>Gmaps Pedometer</Name>
<Trackpoint>
<Time>2007-04-27T06:19:36.9716432Z</Time>
<Position>
<LatitudeDegrees>45.34645</LatitudeDegrees>
<LongitudeDegrees>-73.76584</LongitudeDegrees>
</Position>
<AltitudeMeters>40.87063</AltitudeMeters>
<DistanceMeters>0</DistanceMeters>
<HeartRateBpm>100</HeartRateBpm>
<SensorState>Absent</SensorState>
</Trackpoint>
<Trackpoint>
<Time>2007-04-27T06:19:50.3176432Z</Time>
<Position>
<LatitudeDegrees>45.34695</LatitudeDegrees>
<LongitudeDegrees>-73.76581</LongitudeDegrees>
</Position>
<AltitudeMeters>40.87063</AltitudeMeters>
<DistanceMeters>55.609328583683954</DistanceMeters>
<HeartRateBpm>100</HeartRateBpm>
<SensorState>Absent</SensorState>
</Trackpoint>
<Trackpoint>
<Time>2007-04-27T06:21:54.8456432Z</Time>
<Position>
<LatitudeDegrees>45.34679</LatitudeDegrees>
<LongitudeDegrees>-73.75917</LongitudeDegrees>
</Position>
<AltitudeMeters>39.1546</AltitudeMeters>
<DistanceMeters>574.47532068048974</DistanceMeters>
<HeartRateBpm>100</HeartRateBpm>
<SensorState>Absent</SensorState>
</Trackpoint>
</Track>
^^^^^^^

There is no matching start tag </Track> for that end tag so the XML is not
well-formed.

</Course>
</CourseFolder>
</Courses>
</TrainingCenterDatabase>

With that end tag being removed the following is a complete working
example:

DECLARE @xmlDocument xml;
DECLARE @iDoc int;

SET @xmlDocument = '<TrainingCenterDatabase
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:xsd="http://www.w3.org/2001/XMLSchema";
xmlns="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v1";>
<Courses>
<CourseFolder Name="Courses">
<Course>
<Name>Gmaps Pedometer</Name>
<Trackpoint>
<Time>2007-04-27T06:19:36.9716432Z</Time>
<Position>
<LatitudeDegrees>45.34645</LatitudeDegrees>
<LongitudeDegrees>-73.76584</LongitudeDegrees>
</Position>
<AltitudeMeters>40.87063</AltitudeMeters>
<DistanceMeters>0</DistanceMeters>
<HeartRateBpm>100</HeartRateBpm>
<SensorState>Absent</SensorState>
</Trackpoint>
<Trackpoint>
<Time>2007-04-27T06:19:50.3176432Z</Time>
<Position>
<LatitudeDegrees>45.34695</LatitudeDegrees>
<LongitudeDegrees>-73.76581</LongitudeDegrees>
</Position>
<AltitudeMeters>40.87063</AltitudeMeters>
<DistanceMeters>55.609328583683954</DistanceMeters>
<HeartRateBpm>100</HeartRateBpm>
<SensorState>Absent</SensorState>
</Trackpoint>
<Trackpoint>
<Time>2007-04-27T06:21:54.8456432Z</Time>
<Position>
<LatitudeDegrees>45.34679</LatitudeDegrees>
<LongitudeDegrees>-73.75917</LongitudeDegrees>
</Position>
<AltitudeMeters>39.1546</AltitudeMeters>
<DistanceMeters>574.47532068048974</DistanceMeters>
<HeartRateBpm>100</HeartRateBpm>
<SensorState>Absent</SensorState>
</Trackpoint>
</Course>
</CourseFolder>
</Courses>
</TrainingCenterDatabase>';

EXEC sp_xml_preparedocument @iDoc OUTPUT, @xmlDocument,
'<TrainingCenterDatabase
xmlns:crsns="http://www.garmin.com/xmlschemas/TrainingCenterDatabase/v1"/>';

select * FROM OPENXML (@iDoc,
'/crsns:TrainingCenterDatabase/crsns:Courses/crsns:CourseFolder/crsns:Course/crsns:Trackpoint',2)
WITH (LatitudeDegrees float 'crsns:Position/crsns:LatitudeDegrees',
LongitudeDegrees float 'crsns:Position/crsns:LongitudeDegrees',
DistanceMeters float 'crsns:DistanceMeters',
AltitudeMeters float 'crsns:AltitudeMeters');

EXEC sp_xml_removedocument @iDoc;


--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/


.

Relevant Pages

  • Re: Loading records from XML document
    ... EXEC sp_xml_preparedocument @iDoc OUTPUT, @xmlDocument, ... WITH (LatitudeDegrees float 'crsns:Position/crsns:LatitudeDegrees', ...
    (microsoft.public.sqlserver.xml)
  • Re: Aligning text around image
    ... the after the tag is ignored. ... "left" inside the img tag where I want float left, ... I choose Polesoft Lockspam to fight spam, ...
    (microsoft.public.frontpage.client)
  • Re: XHTML 1.0
    ... If your tables only have 2 cells in them, can't you use another tag to do it? ... } p.left {width: 30%; float: left;} p.right ... Simon wrote: ...
    (microsoft.public.dotnet.framework.aspnet)
  • Loading records from XML document
    ... declare @xmlDocument xml ... EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlDocument, ... WITH (LatitudeDegrees float 'crsns:Position/LatitudeDegrees', ...
    (microsoft.public.sqlserver.xml)
  • Re: Mast All The Way Forward?
    ... forwards of the front strap for float back home. ... Sure, in a pure slalom race, the new stuff can win in most courses. ... the new stuff is better for freestyle than the old stuff. ...
    (rec.windsurfing)