June 29, 2014 at 4:57 am
Hi,
I am loading a whole bunch of GPX files exported from the RunKeeper app.
All files are in format:
<?xml version="1.0" encoding="UTF-8"?>
<gpx
version="1.1"
creator="RunKeeper - http://www.runkeeper.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.topografix.com/GPX/1/1"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd"
xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1">
<trk>
<name><![CDATA[Running 6/21/14 10:05 am]]></name>
<time>2014-06-21T10:05:23Z</time>
<trkseg>
<trkpt lat="55.936098000" lon="-3.198875000"><ele>98.0</ele><time>2014-06-21T10:05:23Z</time></trkpt>
<trkpt lat="55.936145000" lon="-3.198753000"><ele>97.9</ele><time>2014-06-21T10:05:23Z</time></trkpt>
<trkpt lat="55.936201000" lon="-3.198531000"><ele>97.9</ele><time>2014-06-21T10:05:30Z</time></trkpt>
<trkpt lat="55.936101000" lon="-3.194173000"><ele>96.0</ele><time>2014-06-21T11:07:45Z</time></trkpt>
</trkseg>
</trk>
</gpx>
I would like to shred that XML, but I'm struggling with that.
I can't even seem to retrieve bits of the XML, and I don't understand why - I hope someone can help me out.
The table in which I have the data is as follows:
CREATE TABLE [dbo].[TBL_GPXFiles](
[GPXFile_ID] [bigint] IDENTITY(1,1) NOT NULL,
[GPXFile_Name] [nvarchar](500) NOT NULL,
[GPXFile_Contents] [nvarchar](max) NOT NULL,
[GPXFile_XMLContents] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Here is some data:
INSERT INTO [dbo].[TBL_GPXFiles]
([GPXFile_Name]
,[GPXFile_Contents]
,[GPXFile_XMLContents])
VALUES
('file1'
,'<?xml version="1.0" encoding="UTF-8"?><gpx version="1.1" creator="RunKeeper - http://www.runkeeper.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.topografix.com/GPX/1/1" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd" xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1"><trk> <name><![CDATA[Running 6/21/14 10:05 am]]></name> <time>2014-06-21T10:05:23Z</time><trkseg><trkpt lat="55.936098000" lon="-3.198875000"><ele>98.0</ele><time>2014-06-21T10:05:23Z</time></trkpt><trkpt lat="55.936145000" lon="-3.198753000"><ele>97.9</ele><time>2014-06-21T10:05:23Z</time></trkpt><trkpt lat="55.936201000" lon="-3.198531000"><ele>97.9</ele><time>2014-06-21T10:05:30Z</time></trkpt><trkpt lat="55.936101000" lon="-3.194173000"><ele>96.0</ele><time>2014-06-21T11:07:45Z</time></trkpt></trkseg></trk></gpx>'
,'<?xml version="1.0" encoding="UTF-8"?><gpx version="1.1" creator="RunKeeper - http://www.runkeeper.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.topografix.com/GPX/1/1" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd" xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1"><trk> <name><![CDATA[Running 6/21/14 10:05 am]]></name> <time>2014-06-21T10:05:23Z</time><trkseg><trkpt lat="55.936098000" lon="-3.198875000"><ele>98.0</ele><time>2014-06-21T10:05:23Z</time></trkpt><trkpt lat="55.936145000" lon="-3.198753000"><ele>97.9</ele><time>2014-06-21T10:05:23Z</time></trkpt><trkpt lat="55.936201000" lon="-3.198531000"><ele>97.9</ele><time>2014-06-21T10:05:30Z</time></trkpt><trkpt lat="55.936101000" lon="-3.194173000"><ele>96.0</ele><time>2014-06-21T11:07:45Z</time></trkpt></trkseg></trk></gpx>')
GO
And I was hoping to be able to query the XML with:
SELECT GPXFile_Name
, GPXFile_XMLContents.query('gpx/trk/trkseg/trkpt') as col1
FROM dbo.TBL_GPXFiles
At the moment, I guess I don't understand how to retrieve anything from the XML column - if someone could show me how to query from it with one example, that would be great!
Cheers,
B
June 29, 2014 at 5:24 am
This is simply a question of getting the namespaces right, this should get you passed the hurdle
😎
USE tempdb;
GO
DECLARE @TXML XML = '<?xml version="1.0" encoding="UTF-8"?>
<gpx
version="1.1"
creator="RunKeeper - http://www.runkeeper.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.topografix.com/GPX/1/1"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd"
xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1">
<trk>
<name><![CDATA[Running 6/21/14 10:05 am]]></name>
<time>2014-06-21T10:05:23Z</time>
<trkseg>
<trkpt lat="55.936098000" lon="-3.198875000"><ele>98.0</ele><time>2014-06-21T10:05:23Z</time></trkpt>
<trkpt lat="55.936145000" lon="-3.198753000"><ele>97.9</ele><time>2014-06-21T10:05:23Z</time></trkpt>
<trkpt lat="55.936201000" lon="-3.198531000"><ele>97.9</ele><time>2014-06-21T10:05:30Z</time></trkpt>
<trkpt lat="55.936101000" lon="-3.194173000"><ele>96.0</ele><time>2014-06-21T11:07:45Z</time></trkpt>
</trkseg>
</trk>
</gpx>'
;WITH XMLNAMESPACES (
'http://www.topografix.com/GPX/1/1' AS NS1
,DEFAULT 'http://www.garmin.com/xmlschemas/TrackPointExtension/v1'
)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS TRK_RID
,GP.X.value('@creator','VARCHAR(50)') AS Creator
,GP.X.value('@version','VARCHAR(12)') AS Version
,TR.K.value('NS1:name[1]','VARCHAR(50)') AS TRKName
,TR.K.value('NS1:time[1]','DATETIME') AS TRKTime
,TRK.PT.value('@lat','FLOAT') AS TPLatitude
,TRK.PT.value('@lon','FLOAT') AS TPLongitude
,TRK.PT.value('NS1:ele[1]','FLOAT') AS TPElement
,TRK.PT.value('NS1:time[1]','DATETIME') AS TPTime
FROM @TXML.nodes('NS1:gpx') AS GP(X)
OUTER APPLY GP.X.nodes('NS1:trk') AS TR(K)
OUTER APPLY TR.K.nodes('NS1:trkseg/NS1:trkpt') AS TRK(PT)
Results
TRK_RID Creator Version TRKName TRKTime TPLatitude TPLongitude TPElement TPTime
-------- ------------------------------------- --------- ------------------------- ----------------------- ----------- ------------ ---------- -----------------------
1 RunKeeper - http://www.runkeeper.com 1.1 Running 6/21/14 10:05 am 2014-06-21 10:05:23.000 55.936098 -3.198875 98 2014-06-21 10:05:23.000
2 RunKeeper - http://www.runkeeper.com 1.1 Running 6/21/14 10:05 am 2014-06-21 10:05:23.000 55.936145 -3.198753 97.9 2014-06-21 10:05:23.000
3 RunKeeper - http://www.runkeeper.com 1.1 Running 6/21/14 10:05 am 2014-06-21 10:05:23.000 55.936201 -3.198531 97.9 2014-06-21 10:05:30.000
4 RunKeeper - http://www.runkeeper.com 1.1 Running 6/21/14 10:05 am 2014-06-21 10:05:23.000 55.936101 -3.194173 96 2014-06-21 11:07:45.000
June 29, 2014 at 5:39 am
Adding a little fun to it
😎
;WITH XMLNAMESPACES (
'http://www.topografix.com/GPX/1/1' AS NS1
,DEFAULT 'http://www.garmin.com/xmlschemas/TrackPointExtension/v1'
)
,TRACKING_INFO AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY TRK.PT.value('NS1:time[1]','DATETIME')) AS TRK_RID
,GP.X.value('@creator','VARCHAR(50)') AS Creator
,GP.X.value('@version','VARCHAR(12)') AS Version
,TR.K.value('NS1:name[1]','VARCHAR(50)') AS TRKName
,TR.K.value('NS1:time[1]','DATETIME') AS TRKTime
,TRK.PT.value('@lat','FLOAT') AS TPLatitude
,TRK.PT.value('@lon','FLOAT') AS TPLongitude
,TRK.PT.value('NS1:ele[1]','FLOAT') AS TPElement
,TRK.PT.value('NS1:time[1]','DATETIME') AS TPTime
,GEOGRAPHY::Point(TRK.PT.value('@lat','FLOAT'),TRK.PT.value('@lon','FLOAT'),4326) AS GEOPoint
FROM @TXML.nodes('NS1:gpx') AS GP(X)
OUTER APPLY GP.X.nodes('NS1:trk') AS TR(K)
OUTER APPLY TR.K.nodes('NS1:trkseg/NS1:trkpt') AS TRK(PT)
)
SELECT
TI.TRK_RID
,TI.Creator
,TI.Version
,TI.TRKName
,TI.TRKTime
,TI.TPLatitude
,TI.TPLongitude
,TI.TPElement
,TI.TPTime
,TI.GEOPoint.STBuffer(2)
,(LAG(TI.GEOPoint,1,TI.GEOPoint) OVER
(
ORDER BY TI.TRK_RID
)).STDistance(TI.GEOPoint) AS DIST_FROM_LAST
FROM TRACKING_INFO TI;
June 29, 2014 at 5:52 am
Thanks a lot Eirikur! This is what I needed.
And I really like your choice of fun! this is exactly the kind of stuff I want to do with it 🙂
B
June 29, 2014 at 9:32 am
bleroy (6/29/2014)
Thanks a lot Eirikur! This is what I needed.And I really like your choice of fun! this is exactly the kind of stuff I want to do with it 🙂
B
You are welcome.
Kind of obvious though that this kind of fun was to follow:w00t:
😎
February 13, 2015 at 6:50 am
This looks really useful.
How would you get out the HR info for this GPX file though.
DECLARE @TXML XML
SET @TXML = '<?xml version="1.0" encoding="UTF-8"?>
<gpx creator="strava.com iPhone" version="1.1" xmlns="http://www.topografix.com/GPX/1/1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd http://www.garmin.com/xmlschemas/GpxExtensions/v3 http://www.garmin.com/xmlschemas/GpxExtensionsv3.xsd http://www.garmin.com/xmlschemas/TrackPointExtension/v1 http://www.garmin.com/xmlschemas/TrackPointExtensionv1.xsd http://www.garmin.com/xmlschemas/GpxExtensions/v3 http://www.garmin.com/xmlschemas/GpxExtensionsv3.xsd http://www.garmin.com/xmlschemas/TrackPointExtension/v1 http://www.garmin.com/xmlschemas/TrackPointExtensionv1.xsd http://www.garmin.com/xmlschemas/GpxExtensions/v3 http://www.garmin.com/xmlschemas/GpxExtensionsv3.xsd http://www.garmin.com/xmlschemas/TrackPointExtension/v1 http://www.garmin.com/xmlschemas/TrackPointExtensionv1.xsd" xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1" xmlns:gpxx="http://www.garmin.com/xmlschemas/GpxExtensions/v3">
<metadata>
<time>2015-02-13T13:00:18Z</time>
</metadata>
<trk>
<name>Test</name>
<trkseg>
<trkpt lat="51.3382200" lon="-1.5102530">
<ele>93.8</ele>
<time>2014-02-13T13:00:18Z</time>
<extensions>
<gpxtpx:TrackPointExtension>
<gpxtpx:hr>82</gpxtpx:hr>
</gpxtpx:TrackPointExtension>
</extensions>
</trkpt>
<trkpt lat="51.3382550" lon="-1.5103870">
<ele>93.6</ele>
<time>2014-02-13T13:00:23Z</time>
<extensions>
<gpxtpx:TrackPointExtension>
<gpxtpx:hr>82</gpxtpx:hr>
</gpxtpx:TrackPointExtension>
</extensions>
</trkpt>
</trkseg>
</trk>
</gpx>
'
;WITH XMLNAMESPACES (
'http://www.topografix.com/GPX/1/1' AS NS1
,DEFAULT 'http://www.garmin.com/xmlschemas/TrackPointExtension/v1'
)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS TRK_RID
,GP.X.value('@creator','VARCHAR(50)') AS Creator
,GP.X.value('@version','VARCHAR(12)') AS Version
,TR.K.value('NS1:name[1]','VARCHAR(50)') AS TRKName
--,TR.K.value('NS1:time[1]','DATETIME') AS TRKTime
,TRK.PT.value('@lat','FLOAT') AS TPLatitude
,TRK.PT.value('@lon','FLOAT') AS TPLongitude
,TRK.PT.value('NS1:ele[1]','FLOAT') AS TPElement
,TRK.PT.value('NS1:time[1]','DATETIME') AS TPTime
FROM @TXML.nodes('NS1:gpx') AS GP(X)
OUTER APPLY GP.X.nodes('NS1:trk') AS TR(K)
OUTER APPLY TR.K.nodes('NS1:trkseg/NS1:trkpt') AS TRK(PT)
February 13, 2015 at 3:26 pm
Building on Eirikur's solution here's a couple ways to do this without the outer apply...
If there only 1 trk element and only 1 trkseg with trkpt child elements you could use one of these queries...
;WITH XMLNAMESPACES (
'http://www.topografix.com/GPX/1/1' AS NS1
,DEFAULT 'http://www.garmin.com/xmlschemas/TrackPointExtension/v1'
)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS TRK_RID
,GP.X.value('../../../@creator','VARCHAR(50)') AS Creator
,GP.X.value('../../../@version','VARCHAR(12)') AS Version
,GP.X.value('../../NS1:name[1]','VARCHAR(50)') AS TRKName
,GP.X.value('@lat','FLOAT') AS TPLatitude
,GP.X.value('@lon','FLOAT') AS TPLongitude
,GP.X.value('NS1:ele[1]','FLOAT') AS TPElement
,GP.X.value('NS1:time[1]','DATETIME') AS TPTime
FROM @TXML.nodes('NS1:gpx/NS1:trk/NS1:trkseg/NS1:trkpt') AS GP(X)
ORDER BY TRK_RID;-- this is not necessary but returns TRK_RID in the correct order
;WITH XMLNAMESPACES (
'http://www.topografix.com/GPX/1/1' AS NS1
,DEFAULT 'http://www.garmin.com/xmlschemas/TrackPointExtension/v1'
)
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS TRK_RID
,@TXML.value('(NS1:gpx/@creator)[1]','VARCHAR(50)') AS Creator
,@TXML.value('(NS1:gpx/@version)[1]','VARCHAR(12)') AS Version
,@TXML.value('(NS1:gpx/NS1:trk/NS1:name/text()[1])[1]','VARCHAR(50)') AS TRKName
,GP.X.value('@lat','FLOAT') AS TPLatitude
,GP.X.value('@lon','FLOAT') AS TPLongitude
,GP.X.value('(NS1:ele[1]/text())[1]','FLOAT') AS TPElement
,GP.X.value('(NS1:time[1]/text())[1]','DATETIME') AS TPTime
FROM @TXML.nodes('NS1:gpx/NS1:trk/NS1:trkseg/NS1:trkpt') AS GP(X);
I looked up the schema and Eirikur's solution will guarantee the correct answer. I had a few minutes to kill and wanted to show a couple other ways to attack this based on what you posted.
P.S. I use runkeeper. Great app! 😉
-- Itzik Ben-Gan 2001
February 14, 2015 at 8:49 am
To 'complete' this thread, this is some of the stuff that I've done with it:
- download your gpx files from runkeeper (will be a whole bunch of files, one for each activity (with GPS data) and then 2 additional files listing the runs etc)
- I have a database in which to load the data, with views, stored procedures to import the data, export it based on some criteria (e.g. "running activities only" in a specific "location"), also some views used by SSRS report
- a SSIS script imports all the files in the DB
Feel free to reuse & improve upon .. it's not great but hopefully will be useful to someone.
(rename file Import GPX Data (destructive load) - share.txt to use extension dtsx instead
and also change the extension of MainDashboard to rdl)
Using the DB, I can then easily export a subset of activities and view in another tool such as Elevation (http://exnihilo.mezzoblue.com/elevation/) or Google Earth.
In a way trying to emulate some of the stuff seen on http://labs.strava.com
That's one of the limitations of RunKeeper export is that you can not choose "running only" in a specific location (you can only do date range)
March 20, 2022 at 2:35 am
Hi, I am looking for help. I want to track all my walks and runs that I do. I record them all on my Garmin watch, Garmin erred gps and phone app like Gaiagps. I am very new to sql coding so dont really have a clue to be honest. My son knows a bit of sql.
What I would like to do is build a database that records all my run and walk activities by importing my gpl files to a database that it then breaks out all metric of data from the run or walk to be shown on a nice format to read including the map of the track I did.
I dont really want to manually enter all the data after the run or walk. I would really like to just use the gpx file that has all the data
Would appreciate any help to try do this. Thanks Bryan
March 20, 2022 at 12:27 pm
Hi, I am looking for help. I want to track all my walks and runs that I do. I record them all on my Garmin watch, Garmin erred gps and phone app like Gaiagps. I am very new to sql coding so dont really have a clue to be honest. My son knows a bit of sql.
What I would like to do is build a database that records all my run and walk activities by importing my gpl files to a database that it then breaks out all metric of data from the run or walk to be shown on a nice format to read including the map of the track I did.
I dont really want to manually enter all the data after the run or walk. I would really like to just use the gpx file that has all the data
Would appreciate any help to try do this. Thanks Bryan
Hi and welcome to the forum. I'm happy to help, but I will need some more information to do so. Can you please tell us which version and edition of SQL Server you are using and also, post an example of the data files?
😎
You might want to start a new thread rather than trailing on this 7 year old one 😉
March 21, 2022 at 3:55 am
Thanks Eirikur Eiriksson. Appreciate the offer to help. I will start a new post new and give as much info as I can. Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply