Home All Groups Group Topic Archive Search About

Loading records from XML document

microsoft.public.sqlserver.xml
Author
27 Apr 2007 7:43 AM
Alain Quesnel
I'm having a hard time figuring out what I'm doing wrong. I've tried several
versions of this code, and the best I've come up with is the code below. Any
help would be appreciated. Thank you.

DECLARE @idoc int
declare @xmlDocument xml
SET @xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\Documents and Settings\Alain\My Documents\My
Garmin\Chateauguay96km.crs',
SINGLE_BLOB
) AS x
);

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:Track/crsns:Trackpoint',3)
WITH (LatitudeDegrees float 'crsns:Position/LatitudeDegrees',
LongitudeDegrees float 'crsns:Position/LongitudeDegrees',
DistanceMeters float 'crsns:DistanceMeters',
AltitudeMeters float 'crsns:AltitudeMeters')
GO

This yields the following result set:

LatitudeDegrees LongitudeDegrees DistanceMeters AltitudeMeters
---------------------- ---------------------- ---------------------- ----------------------
NULL     NULL     0 40.87063
NULL     NULL     55.609328583684     40.87063
NULL     NULL     574.47532068049     39.1546
....

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>
      </Course>
    </CourseFolder>
  </Courses>
</TrainingCenterDatabase>

--


Alain Quesnel
al***@logiquel.com

www.logiquel.com

Author
27 Apr 2007 10:59 AM
Martin Honnen
Alain Quesnel wrote:

Show quote
> 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/
Author
27 Apr 2007 12:06 PM
Alain Quesnel
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
al***@logiquel.com

www.logiquel.com


Show quote
"Martin Honnen" <mahotr***@yahoo.de> wrote in message
news:en%23VisLiHHA.872@TK2MSFTNGP03.phx.gbl...
> 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/

AddThis Social Bookmark Button

Post Other interesting topics
How to output table as XML with elements on separate lines and indented?
modify() Top-level attribute nodes are not supported Error
Can WordML be put in a SQL Server XML column?
Calling SQL Native Web Service failed as unauthorized
Query xml field in Query Plan DMV