question

NaomiNNN avatar image
0 Votes"
NaomiNNN asked Monalv-msft edited

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

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.

sql-server-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @NaomiNNN,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @NaomiNNN ,

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NaomiNNN ,

May I know if you have anything to update?

Best Regards,
Mona

0 Votes 0 ·

Hi Mona,

I didn't have a chance to try Russel's idea and left our current method instead (just changed from VB to C# code).

Thanks.

0 Votes 0 ·

Hi @NaomiNNN ,

If you have anything new to update or you find the solution, please don't hesitate to contact us. Thank you.

Best regards,
Mona

0 Votes 0 ·
RusselLoski-0634 avatar image
1 Vote"
RusselLoski-0634 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.