Export the data from iphone to an XML file – email to yourself
– Open Heart App
– Tap icon at top right of screen
– Tap “Export Health Data at bottom of screen”
– Tap “Export” – data will be exported, then choose where to send the file.
– For example, email it to yourself.
– The result is a zip file which can be copied to your computer hard drive, unzip to expose the export.xml file.
Copy the file to the SQL Server host server
In my example below, the SQL Server is on Oracle Enterprise Linux 7.4 (SQL Server 2017 can be installed on Linux Install SQL Server on Linux).
Copy the file to /var/opt/mssql/
The file is now /var/opt/mssql/export.xml on the SQL Server.
Import the Data into a SQL Server Table
Create a Database
CREATE DATABASE OPENXMLTesting
GO
Create a Table to Store the Data
USE OPENXMLTesting
GO
--Create a table to store the data
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
Insert the file into the Table – Creates one row
-- The file is an XML file with internal subset DTDs
-- For SQL Server, it has to be parsed and imported with style option 2
-- insert the whole file into the table
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn,2) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK '/var/opt/mssql/export.xml', SINGLE_BLOB) AS x;
Query the data from the table
-- Query the data from the table
-- run this all at same time
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
select T2.RecordType, T2.Unit, Value,
[creationDate]=convert(datetime,left(T2.creationDate,19)),
[startDate]=convert(datetime,left(T2.startDate,19))
from
(
SELECT T.RecordType, T.Unit, T.Value, T.creationDate, T.startDate
FROM OPENXML(@hDoc, 'HealthData/Record')
WITH
(
RecordType [varchar](50) '@type',
Unit [varchar](10) '@unit',
Value [varchar](10) '@value',
creationDate [varchar](30) '@creationDate',
startDate [varchar](30) '@startDate'
) T
) T2
EXEC sp_xml_removedocument @hDoc
GO
Create a Table to Analyze the Data (Example)
Once the xml data is in a database table, many queries can be executed on it to extract useful information.
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
-- creates a table at same time
-- converts date strings into dates
select T2.RecordType, T2.Unit, Value,
[creationDate]=convert(datetime,left(T2.creationDate,19)),
[startDate]=convert(datetime,left(T2.startDate,19))
into HeartData_22102018
from
(
SELECT T.RecordType, T.Unit, T.Value, T.creationDate, T.startDate
FROM OPENXML(@hDoc, 'HealthData/Record')
WITH
(
RecordType [varchar](50) '@type',
Unit [varchar](10) '@unit',
Value decimal '@value',
creationDate [varchar](30) '@creationDate',
startDate [varchar](30) '@startDate'
) T
) T2
EXEC sp_xml_removedocument @hDoc
GO
Query the Data (Example – steps per day)
SELECT [Day]=datepart(day, startDate),
[Month]=datepart(month, startDate),
[Year]=datepart(year, startDate),
[Steps]=sum([Value])
FROM [OPENXMLTesting].[dbo].[HeartData_22102018]
where [RecordType]='HKQuantityTypeIdentifierStepCount'
group by datepart(day, startDate),
datepart(month, startDate),
datepart(year, startDate)
order by datepart(year, startDate) desc,
datepart(month, startDate) desc,
datepart(day, startDate) desc
Day Month Year Steps
22 10 2018 1180
21 10 2018 15034
20 10 2018 23323
19 10 2018 3224
18 10 2018 10083
17 10 2018 7774
16 10 2018 3262