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