– Open Heart App
\n– Tap icon at top right of screen
\n– Tap “Export Health Data at bottom of screen”
\n– Tap “Export” – data will be exported, then choose where to send the file.
\n– For example, email it to yourself.
\n– The result is a zip file which can be copied to your computer hard drive, unzip to expose the export.xml file.<\/p>\n
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<\/a>).<\/p>\n Copy the file to \/var\/opt\/mssql\/<\/em><\/p>\n The file is now \/var\/opt\/mssql\/export.xml<\/em> on the SQL Server.<\/p>\n <\/p>\n Once the xml data is in a database table, many queries can be executed on it to extract useful information.<\/p>\n 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 … Continue reading Import the Data into a SQL Server Table<\/h1>\n
Create a Database<\/h2>\n
\r\nCREATE DATABASE OPENXMLTesting\r\nGO\r\n<\/pre>\n
Create a Table to Store the Data<\/h2>\n
\r\nUSE OPENXMLTesting\r\nGO\r\n\r\n--Create a table to store the data\r\nCREATE TABLE XMLwithOpenXML\r\n(\r\nId INT IDENTITY PRIMARY KEY,\r\nXMLData XML,\r\nLoadedDateTime DATETIME\r\n)\r\n<\/pre>\n
Insert the file into the Table – Creates one row<\/h2>\n
\r\n-- The file is an XML file with internal subset DTDs\r\n-- For SQL Server, it has to be parsed and imported with style option 2\r\n\r\n-- insert the whole file into the table\r\nINSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)\r\nSELECT CONVERT(XML, BulkColumn,2) AS BulkColumn, GETDATE() \r\nFROM OPENROWSET(BULK '\/var\/opt\/mssql\/export.xml', SINGLE_BLOB) AS x;\r\n<\/pre>\n
Query the data from the table<\/h2>\n
\r\n-- Query the data from the table\r\n-- run this all at same time\r\nDECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)\r\n\r\n\r\nSELECT @XML = XMLData FROM XMLwithOpenXML\r\n\r\nEXEC sp_xml_preparedocument @hDoc OUTPUT, @XML\r\n\r\nselect T2.RecordType, T2.Unit, Value,\r\n[creationDate]=convert(datetime,left(T2.creationDate,19)),\r\n[startDate]=convert(datetime,left(T2.startDate,19))\r\nfrom\r\n(\r\nSELECT T.RecordType, T.Unit, T.Value, T.creationDate, T.startDate\r\nFROM OPENXML(@hDoc, 'HealthData\/Record')\r\nWITH \r\n(\r\nRecordType [varchar](50) '@type',\r\nUnit [varchar](10) '@unit',\r\nValue [varchar](10) '@value',\r\ncreationDate [varchar](30) '@creationDate',\r\nstartDate [varchar](30) '@startDate'\r\n) T\r\n) T2\r\n\r\nEXEC sp_xml_removedocument @hDoc\r\nGO\r\n<\/pre>\n
Create a Table to Analyze the Data (Example)<\/h2>\n
\r\nSELECT @XML = XMLData FROM XMLwithOpenXML\r\n\r\nEXEC sp_xml_preparedocument @hDoc OUTPUT, @XML\r\n\r\n-- creates a table at same time\r\n-- converts date strings into dates\r\nselect T2.RecordType, T2.Unit, Value,\r\n[creationDate]=convert(datetime,left(T2.creationDate,19)),\r\n[startDate]=convert(datetime,left(T2.startDate,19))\r\ninto HeartData_22102018\r\nfrom\r\n(\r\nSELECT T.RecordType, T.Unit, T.Value, T.creationDate, T.startDate\r\nFROM OPENXML(@hDoc, 'HealthData\/Record')\r\nWITH \r\n(\r\nRecordType [varchar](50) '@type',\r\nUnit [varchar](10) '@unit',\r\nValue decimal '@value',\r\ncreationDate [varchar](30) '@creationDate',\r\nstartDate [varchar](30) '@startDate'\r\n) T\r\n) T2\r\n\r\nEXEC sp_xml_removedocument @hDoc\r\nGO\r\n<\/pre>\n
Query the Data (Example – steps per day)<\/h2>\n
\r\nSELECT [Day]=datepart(day, startDate),\r\n [Month]=datepart(month, startDate),\r\n\t [Year]=datepart(year, startDate),\r\n [Steps]=sum([Value])\r\n FROM [OPENXMLTesting].[dbo].[HeartData_22102018]\r\n where [RecordType]='HKQuantityTypeIdentifierStepCount'\r\n group by datepart(day, startDate),\r\n datepart(month, startDate),\r\n\t datepart(year, startDate) \r\n order by datepart(year, startDate) desc,\r\n datepart(month, startDate) desc,\r\n\t datepart(day, startDate) desc\r\n<\/pre>\n
\r\nDay\tMonth\tYear\tSteps\r\n22\t10\t2018\t1180\r\n21\t10\t2018\t15034\r\n20\t10\t2018\t23323\r\n19\t10\t2018\t3224\r\n18\t10\t2018\t10083\r\n17\t10\t2018\t7774\r\n16\t10\t2018\t3262\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"