Software Recommendations Stack Exchange is a question and answer site for people seeking specific software recommendations. It only takes a minute to sign up.
Sign up to join this community
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):
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.
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.
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.
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
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.
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/
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
