Apple Health XML to SQL Server

Question

I want to process XML data I've imported from exported Apple Health XML file. The XML data is stored in an import table and I'm trying to use sp_xml_preparedocument to prepare the document and query the data.

The approach described below worked for another part of the XML file, specifically activity summary. Now I receive the following error:

Msg 6603, Level 16, State 2, Line 23
XML parsing error: NodeTest expected here.

@-->[<--type]

The examples given by Microsoft in their documentation seems to be working on a differently structured data, so not sure how or if I could adapt one of their examples to my needs.

I have tried the following:

DECLARE @XML AS XML;
DECLARE @hDoc AS INT;

SELECT @XML = XMLData FROM [Data].AppleImport;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT [type],
       sourceName,
       sourceVersion,
       unit,
       creationDate,
       startDate,
       endDate,
       [value]
FROM OPENXML(@hDoc, 'HealthData/Record')
WITH
(
    [type] NVARCHAR(50) '@[type]',
    sourceName NVARCHAR(50) '@sourceName',
    sourceVersion NVARCHAR(50) '@sourceVersion',
    unit NVARCHAR(50) '@unit',
    creationDate NVARCHAR(50) '@creationDate',
    startDate NVARCHAR(50) '@startDate',
    endDate NVARCHAR(50) '@endDate',
    [value] NVARCHAR(50) '@[value]'
)

EXEC sp_xml_removedocument @hDoc;

The XML Data structure (extracted parts for brewity):

<HealthData locale="nb_NO">
    <ExportDate value="2020-09-03 12:10:19 +0200"/>
    <Me HKCharacteristicTypeIdentifierDateOfBirth="1988-01-01" HKCharacteristicTypeIdentifierBiologicalSex="HKBiologicalSexMale" HKCharacteristicTypeIdentifierBloodType="HKBloodTypeNotSet" HKCharacteristicTypeIdentifierFitzpatrickSkinType="HKFitzpatrickSkinTypeNotSet"/>
    <Record type="HKQuantityTypeIdentifierHeight" sourceName="amsten sin iPhone" sourceVersion="13.0" unit="cm" creationDate="2019-09-23 15:19:48 +0200" startDate="2019-09-23 15:19:48 +0200" endDate="2019-09-23 15:19:48 +0200" value="188"/>
    <Record type="HKQuantityTypeIdentifierHeight" sourceName="Helse" sourceVersion="11.4.1" unit="cm" creationDate="2018-07-31 21:42:50 +0200" startDate="2018-07-31 21:42:50 +0200" endDate="2018-07-31 21:42:50 +0200" value="188"/>
    <MetadataEntry key="HKWasUserEntered" value="1"/>
    </Record>
    <Record type="HKQuantityTypeIdentifierHeartRate" sourceName="amsten sin Apple Watch" sourceVersion="5.3.1" device="&lt;&lt;HKDevice: 0x2834d03c0&gt;, name:Apple Watch, manufacturer:Apple Inc., model:Watch, hardware:Watch4,4, software:5.3.1&gt;" unit="count/min" creationDate="2019-09-15 06:38:32 +0200" startDate="2019-09-15 06:30:08 +0200" endDate="2019-09-15 06:30:08 +0200" value="49">
    <MetadataEntry key="HKMetadataKeyHeartRateMotionContext" value="1"/>
    </Record>
</HealthData>

Any input on how to proceede or good reading materials are most appreciated.

The posted XML is not valid - the second <Record type="HKQuantityTypeIdentifierHeight"/> element is self-closing when it shouldn't be. — AlwaysLearning
– AlwaysLearning, Commented Sep 5, 2020 at 11:12

AlwaysLearning · Accepted Answer · 2020-09-05 11:15:18Z

0

When using valid XML also try using the following OPENXML statement:

SELECT [type],
       sourceName,
       sourceVersion,
       unit,
       creationDate,
       startDate,
       endDate,
       [value]
FROM OPENXML(@hDoc, 'HealthData/Record')
WITH
(
    [type] NVARCHAR(50) '@type',
    sourceName NVARCHAR(50) '@sourceName',
    sourceVersion NVARCHAR(50) '@sourceVersion',
    unit NVARCHAR(50) '@unit',
    creationDate NVARCHAR(50) '@creationDate',
    startDate NVARCHAR(50) '@startDate',
    endDate NVARCHAR(50) '@endDate',
    [value] NVARCHAR(50) '@value'
)

Note that '@[type]' and '@[value]' have changed to '@type' and '@value'.

answered Sep 5, 2020 at 11:15

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Apple Health XML to SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related