3

I am looking for a program, script or web service that can take one XML file as input, and build a relational database from it (schema + populating it). Ideally, MySQL, MariaDB or Postgres.

Microsoft Windows, Mac OS X or Linux are ok. Any license and price is fine.


Two examples of XML files (they both have the same structure):

  • 1
    Are you OK with any XML format? – Nicolas Raoul Jul 1 '16 at 6:56
  • 1
    @PIMP_JUICE_IT Sure, I added two examples of XML files in the question. – Franck Dernoncourt Jul 2 '16 at 2:30
  • 1
    @NicolasRaoul Do you mean XML structure, e.g. as defined as a DTD? – Franck Dernoncourt Jul 2 '16 at 2:40
3

Convert XML to a relational database

I am looking for a program, script or web service that can take one XML file as input, and build a relational database from it (schema + populating it). Ideally, MySQL, MariaDB or Postgres.

Microsoft Windows, Mac OS X or Linux are ok. Any license and price is fine.

The Microsoft SQL Server Method

Below is what I came up with using the Microsoft SQL Server method since you said "Microsoft Windows" would be ok. Please note that I'm not familiar with taking XML data from a file and having it import the schema or column definitions so I did have to map this out a bit to create the table and the OPENXML logic to tell it which elements and attributes to import, and it also needs to know how to traverse the XML nodes and so on too. As long as your XML files are consistent, you should be able to easily expand (or adjust) on this since for the most part, I've done the hard part of it already.


Notes

I based the below logic on the smaller sample data only (~ (< 1 MB)), and below is some TSQL logic you can use from SQL Server Management Studio to import the data from the XML file(s) somewhere on the network or a local drive.

I did try to make this dynamic SQL to be able to execute and just loop through multiple XML files, but due to all the XML elements, attributes, and correlated logic, there were issues with limits of bytes in a dynamically executed query so I didn't have enough time figure out a workaround.

Since this is based on the smaller sample data only, if there are more nodes in the other data than this logic will build for certain fields (e.g. ../../MeshHeadingList/MeshHeading/DescriptorName having 23 nodes in the smaller file), then the logic will need to be adjusted to accommodate accordingly.


The T-SQL Logic

1. Create the table

Run the below logic on the DB you want to create the table that'll import the data from the XML file(s). If you want the table name to be something different than MedlineXMLDataImport then change that in the below logic (where is shows CREATE TABLE MedlineXMLDataImport () to the table name you wish to make it.

CREATE TABLE MedlineXMLDataImport (
        [Owner]                NVARCHAR(25)
        ,[Status]              NVARCHAR(25)
        ,PMID                  INT         
        ,PMID_Version          INT         
        ,CreatedYear           INT         
        ,CreatedMonth          INT         
        ,CreatedDay            INT         
        ,CompletedYear         INT         
        ,CompletedMonth        INT         
        ,CompletedDay          INT         
        ,RevisedYear           INT         
        ,RevisedMonth          INT         
        ,RevisedDay            INT         
        ,PubModel              NVARCHAR(25)
        ,ISSN                  NVARCHAR(25)
        ,IssnType              NVARCHAR(25)
        ,CitedMedium           NVARCHAR(25)
        ,Volume                INT         
        ,Issue                 NVARCHAR(10)
        ,PubYear               INT         
        ,PubMonth              NVARCHAR(5) 
        ,PubDay                INT 
        ,Title                 NVARCHAR(100)
        ,ISOAbbreviation       NVARCHAR(50)
        ,ArticleTitle          NVARCHAR(MAX)
        ,MedlinePgn            NVARCHAR(25)
        ,AbstractText          NVARCHAR(MAX)
        ,AuthorComplete        NVARCHAR(1) 
        ,Author1Valid          NVARCHAR(1) 
        ,Author1LastName       NVARCHAR(25)
        ,Author1ForeName       NVARCHAR(5) 
        ,Author1Initials       NVARCHAR(5) 
        ,Author2Valid          NVARCHAR(1) 
        ,Author2LastName       NVARCHAR(25)
        ,Author2ForeName       NVARCHAR(5) 
        ,Author2Initials       NVARCHAR(5) 
        ,Author3Valid          NVARCHAR(1) 
        ,Author3LastName       NVARCHAR(25)
        ,Author3ForeName       NVARCHAR(5) 
        ,Author3Initials       NVARCHAR(5) 
        ,Author4Valid          NVARCHAR(1) 
        ,Author4LastName       NVARCHAR(25)
        ,Author4ForeName       NVARCHAR(5) 
        ,Author4Initials       NVARCHAR(5) 
        ,Author5Valid          NVARCHAR(1) 
        ,Author5LastName       NVARCHAR(25)
        ,Author5ForeName       NVARCHAR(5) 
        ,Author5Initials       NVARCHAR(5) 
        ,Author6Valid          NVARCHAR(1) 
        ,Author6LastName       NVARCHAR(25)
        ,Author6ForeName       NVARCHAR(5)
        ,Author6Initials       NVARCHAR(5)
        ,[Language]            NVARCHAR(3) 
        ,PublicationTypeUI     NVARCHAR(25)
        ,PublicationType       NVARCHAR(25)
        ,MedlineCountry        NVARCHAR(25)
        ,MedlineTA             NVARCHAR(50)
        ,NlmUniqueID           NVARCHAR(25)
        ,ISSNLinking           NVARCHAR(25)
        ,ChemRegNumber         NVARCHAR(25)
        ,ChemRegUI             NVARCHAR(25)
        ,ChemSubstanceName     NVARCHAR(25)
        ,Chem2RegNumber        NVARCHAR(25)
        ,Chem2RegUI            NVARCHAR(25)
        ,Chem2SubstanceName    NVARCHAR(25)
        ,Chem3RegNumber        NVARCHAR(25)
        ,Chem3RegUI            NVARCHAR(25)
        ,Chem3SubstanceName    NVARCHAR(25)
        ,Chem4RegNumber        NVARCHAR(25)
        ,Chem4RegUI            NVARCHAR(25)
        ,Chem4SubstanceName    NVARCHAR(25)
        ,Chem5RegNumber        NVARCHAR(25)
        ,Chem5RegUI            NVARCHAR(25)
        ,Chem5SubstanceName    NVARCHAR(25)
        ,Chem6RegNumber        NVARCHAR(25)
        ,Chem6RegUI            NVARCHAR(25)
        ,Chem6SubstanceName    NVARCHAR(25)
        ,Chem7RegNumber        NVARCHAR(25)
        ,Chem7RegUI            NVARCHAR(25)
        ,Chem7SubstanceName    NVARCHAR(25)
        ,Chem8RegNumber        NVARCHAR(25)
        ,Chem8RegUI            NVARCHAR(25)
        ,Chem8SubstanceName    NVARCHAR(25)
        ,Chem9RegNumber        NVARCHAR(25)
        ,Chem9RegUI            NVARCHAR(25)
        ,Chem9SubstanceName    NVARCHAR(25)
        ,Chem10RegNumber       NVARCHAR(25)
        ,Chem10RegUI           NVARCHAR(25)
        ,Chem10SubstanceName   NVARCHAR(25)
        ,Chem11RegNumber       NVARCHAR(25)
        ,Chem11RegUI           NVARCHAR(25)
        ,Chem11SubstanceName   NVARCHAR(25)
        ,Chem12RegNumber       NVARCHAR(25)
        ,Chem12RegUI           NVARCHAR(25)
        ,Chem12SubstanceName   NVARCHAR(25)
        ,CitationSubset        NVARCHAR(5) 
        ,DescriptorName        NVARCHAR(25)
        ,MajorTopic            NVARCHAR(1) 
        ,MeshHeadingUI         NVARCHAR(25)
        ,DescriptorName2       NVARCHAR(25)
        ,MajorTopic2           NVARCHAR(1) 
        ,MeshHeadingUI2        NVARCHAR(25)
        ,DescriptorName3       NVARCHAR(25)
        ,MajorTopic3           NVARCHAR(1) 
        ,MeshHeadingUI3        NVARCHAR(25)
        ,DescriptorName4       NVARCHAR(25)
        ,MajorTopic4           NVARCHAR(1) 
        ,MeshHeadingUI4        NVARCHAR(25)
        ,DescriptorName5       NVARCHAR(25)
        ,MajorTopic5           NVARCHAR(1) 
        ,MeshHeadingUI5        NVARCHAR(25)
        ,DescriptorName6       NVARCHAR(25)
        ,MajorTopic6           NVARCHAR(1) 
        ,MeshHeadingUI6        NVARCHAR(25)
        ,DescriptorName7       NVARCHAR(25)
        ,MajorTopic7           NVARCHAR(1) 
        ,MeshHeadingUI7        NVARCHAR(25)
        ,DescriptorName8       NVARCHAR(25)
        ,MajorTopic8           NVARCHAR(1) 
        ,MeshHeadingUI8        NVARCHAR(25)
        ,DescriptorName9       NVARCHAR(25)
        ,MajorTopic9           NVARCHAR(1) 
        ,MeshHeadingUI9        NVARCHAR(25)
        ,DescriptorName10      NVARCHAR(25)
        ,MajorTopic10          NVARCHAR(1) 
        ,MeshHeadingUI10       NVARCHAR(25)
        ,DescriptorName11      NVARCHAR(25)
        ,MajorTopic11          NVARCHAR(1) 
        ,MeshHeadingUI11       NVARCHAR(25)
        ,DescriptorName12      NVARCHAR(25)
        ,MajorTopic12          NVARCHAR(1) 
        ,MeshHeadingUI12       NVARCHAR(25)
        ,DescriptorName13      NVARCHAR(25)
        ,MajorTopic13          NVARCHAR(1) 
        ,MeshHeadingUI13       NVARCHAR(25)
        ,DescriptorName14      NVARCHAR(25)
        ,MajorTopic14          NVARCHAR(1) 
        ,MeshHeadingUI14       NVARCHAR(25)
        ,DescriptorName15      NVARCHAR(25)
        ,MajorTopic15          NVARCHAR(1) 
        ,MeshHeadingUI15       NVARCHAR(25)
        ,DescriptorName16      NVARCHAR(25)
        ,MajorTopic16          NVARCHAR(1) 
        ,MeshHeadingUI16       NVARCHAR(25)
        ,DescriptorName17      NVARCHAR(25)
        ,MajorTopic17          NVARCHAR(1) 
        ,MeshHeadingUI17       NVARCHAR(25)
        ,DescriptorName18      NVARCHAR(25)
        ,MajorTopic18          NVARCHAR(1) 
        ,MeshHeadingUI18       NVARCHAR(25)
        ,DescriptorName19      NVARCHAR(25)
        ,MajorTopic19          NVARCHAR(1) 
        ,MeshHeadingUI19       NVARCHAR(25)
        ,DescriptorName20      NVARCHAR(25)
        ,MajorTopic20          NVARCHAR(1) 
        ,MeshHeadingUI20       NVARCHAR(25)
        ,DescriptorName21      NVARCHAR(25)
        ,MajorTopic21          NVARCHAR(1) 
        ,MeshHeadingUI21       NVARCHAR(25)
        ,DescriptorName22      NVARCHAR(25)
        ,MajorTopic22          NVARCHAR(1) 
        ,MeshHeadingUI22       NVARCHAR(25)
        ,DescriptorName23      NVARCHAR(25)
        ,MajorTopic23          NVARCHAR(1) 
        ,MeshHeadingUI23       NVARCHAR(25)
    )

2. Import the XML data

Run the below logic on the same DB you created the table on from Step #1 above. Again, if you want the table name to be something different than MedlineXMLDataImport then change that in the below logic as well to the correct table name (where it shows INSERT INTO MedlineXMLDataImport).

Additionally, you will need to plug in the full explicit path and the file name of the file you wish to import with the below logic so you will need to point that to the file you are running this against (where it shows FROM OPENROWSET(BULK '\\server\folder\Test.xml',). The path can be a local path or a UNC path as long as you have access to it from the SQL Server instance you run the query. Unfortunately, you will have to change this part of the logic's value per file you run it against with the way this logic works since I couldn't get it to loop through a folder with multiple XML file with dynamic SQL logic execution as above I briefly mentioned.

Please note that the way this logic is setup, it will allow you to run and import the same data from the same XML files more than once and give you duplicated records in the table so keep this in mind in case it's of importance to you. So if it is, once you import the data, you may want to move the file over to an imported folder or something along those lines.

DECLARE @X XML

SELECT @X = P

FROM OPENROWSET(BULK '\\server\folder\Test.xml', SINGLE_BLOB) AS Products(P)

DECLARE @iX INT

EXEC sp_xml_preparedocument @iX OUTPUT
    ,@X

INSERT INTO MedlineXMLDataImport
SELECT * FROM OPENXML(@iX, '/*/*/*/*', 2) WITH (
    [Owner]                NVARCHAR(25)  '../../@Owner'
    ,[Status]              NVARCHAR(25)  '../../@Status'
    ,PMID                  INT           '../../PMID'
    ,PMID_Version          INT           '../../PMID/@Version'
    ,CreatedYear           INT           '../../DateCreated/Year'
    ,CreatedMonth          INT           '../../DateCreated/Month'
    ,CreatedDay            INT           '../../DateCreated/Day'
    ,CompletedYear         INT           '../../DateCompleted/Year'
    ,CompletedMonth        INT           '../../DateCompleted/Month'
    ,CompletedDay          INT           '../../DateCompleted/Day'
    ,RevisedYear           INT           '../../DateRevised/Year'
    ,RevisedMonth          INT           '../../DateRevised/Month'
    ,RevisedDay            INT           '../../DateRevised/Day'
    ,PubModel              NVARCHAR(25)  '../@PubModel'
    ,ISSN                  NVARCHAR(25)  'ISSN'
    ,IssnType              NVARCHAR(25)  'ISSN/@IssnType'
    ,CitedMedium           NVARCHAR(25)  'JournalIssue/@CitedMedium'
    ,Volume                INT           'JournalIssue/Volume'
    ,Issue                 NVARCHAR(10)  'JournalIssue/Issue'
    ,PubYear               INT           'JournalIssue/PubDate/Year'
    ,PubMonth              NVARCHAR(5)   'JournalIssue/PubDate/Month'
    ,PubDay                INT           'JournalIssue/PubDate/Day'
    ,Title                 NVARCHAR(100) 'Title'
    ,ISOAbbreviation       NVARCHAR(50)  'ISOAbbreviation'
    ,ArticleTitle          NVARCHAR(MAX) '../ArticleTitle'
    ,MedlinePgn            NVARCHAR(25)  '../Pagination/MedlinePgn'
    ,AbstractText          NVARCHAR(MAX) '../Abstract/AbstractText'
    ,AuthorComplete        NVARCHAR(1)   '../AuthorList/@CompleteYN'
    ,Author1Valid          NVARCHAR(1)   '../AuthorList/Author/@ValidYN'
    ,Author1LastName       NVARCHAR(25)  '../AuthorList/Author/LastName'
    ,Author1ForeName       NVARCHAR(5)   '../AuthorList/Author/ForeName'
    ,Author1Initials       NVARCHAR(5)   '../AuthorList/Author/Initials'
    ,Author2Valid          NVARCHAR(1)   '../AuthorList/Author[2]/@ValidYN'
    ,Author2LastName       NVARCHAR(25)  '../AuthorList/Author[2]/LastName'
    ,Author2ForeName       NVARCHAR(5)   '../AuthorList/Author[2]/ForeName'
    ,Author2Initials       NVARCHAR(5)   '../AuthorList/Author[2]/Initials'
    ,Author3Valid          NVARCHAR(1)   '../AuthorList/Author[3]/@ValidYN'
    ,Author3LastName       NVARCHAR(25)  '../AuthorList/Author[3]/LastName'
    ,Author3ForeName       NVARCHAR(5)   '../AuthorList/Author[3]/ForeName'
    ,Author3Initials       NVARCHAR(5)   '../AuthorList/Author[3]/Initials'
    ,Author4Valid          NVARCHAR(1)   '../AuthorList/Author[4]/@ValidYN'
    ,Author4LastName       NVARCHAR(25)  '../AuthorList/Author[4]/LastName'
    ,Author4ForeName       NVARCHAR(5)   '../AuthorList/Author[4]/ForeName'
    ,Author4Initials       NVARCHAR(5)   '../AuthorList/Author[4]/Initials'
    ,Author5Valid          NVARCHAR(1)   '../AuthorList/Author[5]/@ValidYN'
    ,Author5LastName       NVARCHAR(25)  '../AuthorList/Author[5]/LastName'
    ,Author5ForeName       NVARCHAR(5)   '../AuthorList/Author[5]/ForeName'
    ,Author5Initials       NVARCHAR(5)   '../AuthorList/Author[5]/Initials'
    ,Author6Valid          NVARCHAR(1)   '../AuthorList/Author[6]/@ValidYN'
    ,Author6LastName       NVARCHAR(25)  '../AuthorList/Author[6]/LastName'
    ,Author6ForeName       NVARCHAR(5)   '../AuthorList/Author[6]/ForeName'
    ,Author6Initials       NVARCHAR(5)   '../AuthorList/Author[6]/Initials'
    ,[Language]            NVARCHAR(3)   '../Language'
    ,PublicationTypeUI     NVARCHAR(25)  '../PublicationTypeList/PublicationType/@UI'
    ,PublicationType       NVARCHAR(25)  '../PublicationTypeList/PublicationType'
    ,MedlineCountry        NVARCHAR(25)  '../../MedlineJournalInfo/Country'
    ,MedlineTA             NVARCHAR(50)  '../../MedlineJournalInfo/MedlineTA'
    ,NlmUniqueID           NVARCHAR(25)  '../../MedlineJournalInfo/NlmUniqueID'
    ,ISSNLinking           NVARCHAR(25)  '../../MedlineJournalInfo/ISSNLinking'
    ,ChemRegNumber         NVARCHAR(25)  '../../ChemicalList/Chemical/RegistryNumber'
    ,ChemRegUI             NVARCHAR(25)  '../../ChemicalList/Chemical/NameOfSubstance/@UI'
    ,ChemSubstanceName     NVARCHAR(25)  '../../ChemicalList/Chemical/NameOfSubstance'
    ,Chem2RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[2]/RegistryNumber'
    ,Chem2RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[2]/NameOfSubstance/@UI'
    ,Chem2SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[2]/NameOfSubstance'
    ,Chem3RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[3]/RegistryNumber'
    ,Chem3RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[3]/NameOfSubstance/@UI'
    ,Chem3SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[3]/NameOfSubstance'   
    ,Chem4RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[4]/RegistryNumber'
    ,Chem4RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[4]/NameOfSubstance/@UI'
    ,Chem4SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[4]/NameOfSubstance'
    ,Chem5RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[5]/RegistryNumber'
    ,Chem5RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[5]/NameOfSubstance/@UI'
    ,Chem5SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[5]/NameOfSubstance'
    ,Chem6RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[6]/RegistryNumber'
    ,Chem6RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[6]/NameOfSubstance/@UI'
    ,Chem6SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[6]/NameOfSubstance'   
    ,Chem7RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[7]/RegistryNumber'
    ,Chem7RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[7]/NameOfSubstance/@UI'
    ,Chem7SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[7]/NameOfSubstance'   
    ,Chem8RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[8]/RegistryNumber'
    ,Chem8RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[8]/NameOfSubstance/@UI'
    ,Chem8SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[8]/NameOfSubstance'   
    ,Chem9RegNumber        NVARCHAR(25)  '../../ChemicalList/Chemical[9]/RegistryNumber'
    ,Chem9RegUI            NVARCHAR(25)  '../../ChemicalList/Chemical[9]/NameOfSubstance/@UI'
    ,Chem9SubstanceName    NVARCHAR(25)  '../../ChemicalList/Chemical[9]/NameOfSubstance'
    ,Chem10RegNumber       NVARCHAR(25)  '../../ChemicalList/Chemical[10]/RegistryNumber'
    ,Chem10RegUI           NVARCHAR(25)  '../../ChemicalList/Chemical[10]/NameOfSubstance/@UI'
    ,Chem10SubstanceName   NVARCHAR(25)  '../../ChemicalList/Chemical[10]/NameOfSubstance'
    ,Chem11RegNumber       NVARCHAR(25)  '../../ChemicalList/Chemical[11]/RegistryNumber'
    ,Chem11RegUI           NVARCHAR(25)  '../../ChemicalList/Chemical[11]/NameOfSubstance/@UI'
    ,Chem11SubstanceName   NVARCHAR(25)  '../../ChemicalList/Chemical[11]/NameOfSubstance'
    ,Chem12RegNumber       NVARCHAR(25)  '../../ChemicalList/Chemical[12]/RegistryNumber'
    ,Chem12RegUI           NVARCHAR(25)  '../../ChemicalList/Chemical[12]/NameOfSubstance/@UI'
    ,Chem12SubstanceName   NVARCHAR(25)  '../../ChemicalList/Chemical[12]/NameOfSubstance'
    ,CitationSubset        NVARCHAR(5)   '../../CitationSubset'
    ,DescriptorName        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading/DescriptorName'
    ,MajorTopic            NVARCHAR(1)   '../../MeshHeadingList/MeshHeading/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI         NVARCHAR(25)  '../../MeshHeadingList/MeshHeading/DescriptorName/@UI'
    ,DescriptorName2       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[2]/DescriptorName'
    ,MajorTopic2           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[2]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI2        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[2]/DescriptorName/@UI'
    ,DescriptorName3       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[3]/DescriptorName'
    ,MajorTopic3           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[3]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI3        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[3]/DescriptorName/@UI'
    ,DescriptorName4       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[4]/DescriptorName'
    ,MajorTopic4           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[4]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI4        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[4]/DescriptorName/@UI'
    ,DescriptorName5       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[5]/DescriptorName'
    ,MajorTopic5           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[5]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI5        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[5]/DescriptorName/@UI'      
    ,DescriptorName6       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[6]/DescriptorName'
    ,MajorTopic6           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[6]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI6        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[6]/DescriptorName/@UI'
    ,DescriptorName7       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[7]/DescriptorName'
    ,MajorTopic7           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[7]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI7        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[7]/DescriptorName/@UI'
    ,DescriptorName8       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[8]/DescriptorName'
    ,MajorTopic8           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[8]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI8        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[8]/DescriptorName/@UI'
    ,DescriptorName9       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[9]/DescriptorName'
    ,MajorTopic9           NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[9]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI9        NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[9]/DescriptorName/@UI'
    ,DescriptorName10      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[10]/DescriptorName'
    ,MajorTopic10          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[10]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI10       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[10]/DescriptorName/@UI'
    ,DescriptorName11      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[11]/DescriptorName'
    ,MajorTopic11          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[11]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI11       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[11]/DescriptorName/@UI'
    ,DescriptorName12      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[12]/DescriptorName'
    ,MajorTopic12          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[12]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI12       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[12]/DescriptorName/@UI'
    ,DescriptorName13      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[13]/DescriptorName'
    ,MajorTopic13          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[13]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI13       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[13]/DescriptorName/@UI'
    ,DescriptorName14      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[14]/DescriptorName'
    ,MajorTopic14          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[14]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI14       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[14]/DescriptorName/@UI'
    ,DescriptorName15      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[15]/DescriptorName'
    ,MajorTopic15          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[15]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI15       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[15]/DescriptorName/@UI'
    ,DescriptorName16      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[16]/DescriptorName'
    ,MajorTopic16          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[16]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI16       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[16]/DescriptorName/@UI'
    ,DescriptorName17      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[17]/DescriptorName'
    ,MajorTopic17          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[17]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI17       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[17]/DescriptorName/@UI'
    ,DescriptorName18      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[18]/DescriptorName'
    ,MajorTopic18          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[18]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI18       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[18]/DescriptorName/@UI'
    ,DescriptorName19      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[19]/DescriptorName'
    ,MajorTopic19          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[19]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI19       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[19]/DescriptorName/@UI'
    ,DescriptorName20      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[20]/DescriptorName'
    ,MajorTopic20          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[20]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI20       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[20]/DescriptorName/@UI'
    ,DescriptorName21      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[21]/DescriptorName'
    ,MajorTopic21          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[21]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI21       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[21]/DescriptorName/@UI'
    ,DescriptorName22      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[22]/DescriptorName'
    ,MajorTopic22          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[22]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI22       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[22]/DescriptorName/@UI'
    ,DescriptorName23      NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[23]/DescriptorName'
    ,MajorTopic23          NVARCHAR(1)   '../../MeshHeadingList/MeshHeading[23]/DescriptorName/@MajorTopicYN'
    ,MeshHeadingUI23       NVARCHAR(25)  '../../MeshHeadingList/MeshHeading[23]/DescriptorName/@UI'
    )
WHERE ISSN IS NOT NULL

EXEC sp_xml_removedocument @iX
3

You can easily convert XML or JSON to MariaDB / MySQL using Flexter. Have a look at this blog post that shows how to convert TransXchange XML to MariaDB.

Flexter will auto-generate the target schema and keys for you and then auto-populate the schema with the XML data. It also comes with an API for programmatic conversion.

You can also try it for free https://xmlconverter.sonra.io/

2

Importing and Processing data from XML files into SQL Server tables,there are different ways to achieve this task of importing data from an XML file into a SQL Server table,one of easiest ways to accomplish this task is importing data into SQL Server and then parsing the XML into a relational format.

Import XML data from an XML file into SQL Server table using the OPENROWSET function.

Parse the XML data using the OPENXML function.

Examples of Bulk Import and Export of XML Documents (SQL Server) https://msdn.microsoft.com/en-us/library/ms191184.aspx

2

An online tool called 'SQLizer' exists (I work here) which can take XML (or JSON & XLS) and convert it into MySQL table definitions and insert statements. It will also make some intelligent guesses about the column types (INT, VARCHAR, etc).

It's free for small files and $10 for large files.

https://sqlizer.io

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.