Insert of XML file into Database in SQL Server

Question

I have create a table with 3 columns @UserDefinedSectionData with XML datatype, @UserDefinedModuleId with INT datatype and @UserDefinedTabId with INT datatype.

I need to insert the whole XML file into database, in T-SQL i need to read path from my local PC, example: C:\Websites\dnndev.me\XML\abc.xml, after read it and insert the whole xml into UserDefinedSectionData column which is XML datatype.

How can I do it?

McNets · Accepted Answer · 2018-04-12 09:57:26Z

4

I use this syntax:

DECLARE @xml_data XML,
        @path_file nvarchar(260),
        @cmd nvarchar(max);

SET @path_file = N'C:\Folder\File.XML';

/* get XML file */
SET @cmd = 'SELECT @xmlText = BulkColumn FROM OPENROWSET(BULK '
            + '''' + @path_file + ''''
            + ', SINGLE_BLOB) x;';

EXEC sp_executesql @cmd, N'@xmlText XML OUTPUT', @xmlText = @xml_data OUTPUT;

You must grant permission to the account that runs SQL Server agent.

answered Apr 12, 2018 at 9:57

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

McNets Over a year ago

I'm glad to help. Please consider to accept the answer if it works.

Collectives™ on Stack Overflow

Insert of XML file into Database in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related