How to import XML from Data Dictionary to SQL Server?

Question

To put it in context, I’m making a data dictionary for a local database, so I used a tool called "Database Notetaker".

This tool loads the database data and allows me to comment on the table data and save the changes in XML file. Extensive file to put in the text, available Here.

First I need to know if you can import these types of data to SQL Server. If you have, how do I import this?

Answer 1

Yes, it is possible, do the following:

CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)


INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'c:\seuarquivo.xml', SINGLE_BLOB) AS x;


SELECT * FROM XMLwithOpenXML

Once this is done, you will create a table to store the contents of your XML. Now to read this content I made a test with your file:

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

select @xml
SELECT [Key], [Value]
FROM OPENXML(@hDoc, 'DatabaseNoteTakerProject/Project/Setting')
WITH 
(
[key] [varchar](50) '@key',
[value] [varchar](100) '@value'
)

EXEC sp_xml_removedocument @hDoc
GO

I got as a result: (which are part of your XML)

saveConnectionSettingsSeparately False

saveOneFilePerObject False

saveObjectHistory True

publishPath

publishXSLPath

includeFilter NULL

excludeFilter NULL

More information and details can be found here:

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/