Simplest way to import XML file content into XML column in SQL Server using SSIS

Question

What is the best way to import data from XML file into a single XML column in a database? (The whole XML from the file)?

I'd like to use SSIS and I already tried BULK LOAD from OPENROWSET and got an error about file not existing,

Need a different approach. Current way is using script task (in VB.NET) to write content into memory variable and then insert using that variable.

Thanks in advance.

Answer 1

Hi @Naomi ,

The SSIS XML Source Adapter is for shredding XML into a relational/rectangular format.
There is no other task to process input XML file out-of-the-box in SSIS.

Your approach by using

SELECT ...  
FROM OPENROWSET (BULK N'e:\temp\input.xml', SINGLE_BLOB)  

is the best method to load XML file in its entirety.

Please update your question and add the entire error message.

Here is a minimal reproducuble example for you. You can create a stored procedure out of it, passing a single parameter: fully qualified path to XML file - @fileName.

And call that SP in SSIS via Execute SQL Task.

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);  
  
DECLARE @sql NVARCHAR(MAX)  
 , @fileName VARCHAR(255) = N'e:\Temp\input.xml';  
  
SET @sql = N'SELECT * FROM OPENROWSET (BULK ' + QUOTENAME(@fileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';  
  
INSERT INTO @tbl (xmldata)  
EXEC sys.sp_executesql @sql;  
  
-- test  
SELECT * FROM @tbl;  

Obviously, you would need permissions for that operation.
There are two ways to grant permissions for bulk operations.

(1) As a server level permission:

GRANT ADMINISTER BULK OPERATIONS TO [server_login]

(2) There is a Server-level role called [bulkadmin]. So, you could also add a user or group to that role:

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [USERNAME];

(3) SQL Server may also return an error if the service account does not have permission to read the file being loaded. Ensure that the service account has read access to the file location.

Answer 2

Another way to import an xml file into an xml column is to use the Import Column transformation (https://www.tutorialgateway.org/import-column-transformation-in-ssis/). You will need to make sure that you have valid XML.

Answer 3

Hi @Naomi ,

The XML source reads an XML data file and populates the columns in the source output with the data.
The data in XML files frequently includes hierarchical relationships. For example, an XML data file can represent catalogs and items in catalogs. Before the data can enter the data flow, the relationship of the elements in XML data file must be determined, and an output must be generated for each element in the file.

We can use XML source to read the XML data file.

Please refer to Import XML documents into SQL Server tables using SSIS packages.

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Russel,

I'm going to try that method later today (for now I got it using script task, but I'd like to get rid of the script task completely)