sql-server – How to import XML from Data Dictionary to SQL Server?

Question:

To contextualize, I'm making a data dictionary for a local database, for that I used a tool called "DataBase NoteTaker".

This tool loads the data from the database and allows me to comment the data in the tables and save the changes in XML file. Very large file to put in the text, is available here.

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

Answer:

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 that's done, you'll create a table to store your XML content. Now to read this content I did 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/

Scroll to Top