How to create a database from an XML file’s DTD

xml

I have a fairly large and complex self-describing XML file (It's an Apple Healthkit export; 183 MB).

Are there any utilities available to create a database in MySQL or PostgreSQL with the tables and fields described in the XML file's DTD?

(If not, I suppose PHP's SimpleXML Parser would help but would require more thought.)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE HealthData [
<!-- HealthKit Export Version: 3 -->
<!ELEMENT HealthData (ExportDate,Me,(Record|Correlation|Workout|ActivitySummary)*)>
<!ATTLIST HealthData
  locale CDATA #REQUIRED
>
<!ELEMENT ExportDate EMPTY>
<!ATTLIST ExportDate
  value CDATA #REQUIRED
>
<!ELEMENT Me EMPTY>
<!ATTLIST Me
  HKCharacteristicTypeIdentifierDateOfBirth         CDATA #REQUIRED
  HKCharacteristicTypeIdentifierBiologicalSex       CDATA #REQUIRED
  HKCharacteristicTypeIdentifierBloodType           CDATA #REQUIRED
  HKCharacteristicTypeIdentifierFitzpatrickSkinType CDATA #REQUIRED
>
<!ELEMENT Record (MetadataEntry*)>
<!ATTLIST Record
  type          CDATA #REQUIRED
  unit          CDATA #IMPLIED
  value         CDATA #IMPLIED
  sourceName    CDATA #REQUIRED
  sourceVersion CDATA #IMPLIED
  device        CDATA #IMPLIED
  creationDate  CDATA #IMPLIED
  startDate     CDATA #REQUIRED
  endDate       CDATA #REQUIRED
>
<!-- Note: Any Records that appear as children of a correlation also appear as top-level records in this document. -->
<!ELEMENT Correlation ((MetadataEntry|Record)*)>
<!ATTLIST Correlation
  type          CDATA #REQUIRED
  sourceName    CDATA #REQUIRED
  sourceVersion CDATA #IMPLIED
  device        CDATA #IMPLIED
  creationDate  CDATA #IMPLIED
  startDate     CDATA #REQUIRED
  endDate       CDATA #REQUIRED
>
<!ELEMENT Workout ((MetadataEntry|WorkoutEvent)*)>
<!ATTLIST Workout
  workoutActivityType   CDATA #REQUIRED
  duration              CDATA #IMPLIED
  durationUnit          CDATA #IMPLIED
  totalDistance         CDATA #IMPLIED
  totalDistanceUnit     CDATA #IMPLIED
  totalEnergyBurned     CDATA #IMPLIED
  totalEnergyBurnedUnit CDATA #IMPLIED
  sourceName            CDATA #REQUIRED
  sourceVersion         CDATA #IMPLIED
  device                CDATA #IMPLIED
  creationDate          CDATA #IMPLIED
  startDate             CDATA #REQUIRED
  endDate               CDATA #REQUIRED
>
<!ELEMENT WorkoutEvent EMPTY>
<!ATTLIST WorkoutEvent
  type CDATA #REQUIRED
  date CDATA #REQUIRED
>
<!ELEMENT ActivitySummary EMPTY>
<!ATTLIST ActivitySummary
  dateComponents           CDATA #IMPLIED
  activeEnergyBurned       CDATA #IMPLIED
  activeEnergyBurnedGoal   CDATA #IMPLIED
  activeEnergyBurnedUnit   CDATA #IMPLIED
  appleExerciseTime        CDATA #IMPLIED
  appleExerciseTimeGoal    CDATA #IMPLIED
  appleStandHours          CDATA #IMPLIED
  appleStandHoursGoal      CDATA #IMPLIED
>
<!ELEMENT MetadataEntry EMPTY>
<!ATTLIST MetadataEntry
  key   CDATA #REQUIRED
  value CDATA #REQUIRED
>
]>
<HealthData locale="en_GB"> ... etc. (data)

Best Answer

Use the following tools:

1) create full sql schema from DTD.

DTDParse + XML::RDB

2) convert XML doc with data into SQL.

https://stackoverflow.com/questions/13121456/transform-xml-using-xslt-to-sql

References

~~DTD Tools~~ (the link no longer works)
XML Matters: Using Python modules xml2sql and dtd2sql

Related Solutions

Postgresql – SQL to read XML from file into PostgreSQL database

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding).

This function works for any path, but needs to be created as superuser:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

select convert_from(stack.bytea_import('/tmp/test.xml'), 'utf8')::xml;

Sql-server – SQL Database to XML

I am using export data from query (table ) to XML in SQL when I have needs for autonomous transactions.

Way I do: I wrote a SQL-CLR stored procedure which using C# (code) and XML.Writer for writing data to disk

Here is parts of my code in C# (you need to compile this )

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static void NapisiXMLtoFile(string fileName, SqlXml xmlLog )
    {
        try
        {
            if (System.IO.File.Exists(fileName))
            {
                System.IO.File.Delete(fileName);
            }
            File.WriteAllText(fileName, xmlLog.Value.ToString());
        }
        catch (Exception ex)
        {
            SqlContext.Pipe.Send("Error writing to file " + ex.Message);
        }
    }
}

Here is how you register your procedure

CREATE PROCEDURE [dbo].[clr_write_to_file]
    @fileName [nchar](100),
    @xmlLog [xml]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [WriteToFile].[UserDefinedFunctions].[NapisiXMLtoFile]
GO

and finally here is how you call your stored procedure to write file to disk

exec dbo.clr_write_to_file @imeFajla,@xmlLOG

Best Answer

Related Solutions

Postgresql – SQL to read XML from file into PostgreSQL database

Sql-server – SQL Database to XML

Related Question