Load XML file into a SQL Server Table using SSIS 2012

HI!

I'd need to load data from an XML(1.0) file into a SQL Server 2012 Table. It has multiple request and response data elements. What would be the best possible approach in terms of logic and performance. All the data needs to be loaded into just one table.The filename and elements should not be hardcoded.

Any logic/code is greatly appreciated.

Thanks a million in advance for your help!
amuktaAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
BULK command it's also available in T-SQL so you won't need a command line tool like bcp.
INSERT INTO XMLTable(XMLColumn)
SELECT CONVERT(XML, ImportedColumn)
FROM OPENROWSET(BULK 'C:\DataFile.xml', SINGLE_BLOB) AS x

Open in new window

0
 
Ryan ChongCommented:
as a start you can explore to XQuery Language Reference.

XQuery Language Reference (SQL Server)
https://msdn.microsoft.com/en-us/library/ms189075.aspx
0
 
Ryan ChongCommented:
@amukta,

in addition, you can tell us how's your xml source and table looks like.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
amuktaAuthor Commented:
Is there anyway we can load XML file into a SQL server table, using SQL Script and SSIS without using bulk load, as we do not have bulk load permissions on the servers.l
0
 
Ryan ChongCommented:
@amukta,

what Vitor suggested in comment ID: 41791412 should work for you as it runs well in SSIS package as well. but it also depends on how your XML looks like so that you may build in the logic how you extract the data from your XML file.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Amukta, a feedback will be appreciated.
Cheers
0
 
amuktaAuthor Commented:
We do not have permissions to perform the bulk load operation.Could you please as to how can the above code you've posted be used in SSIS?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll need INSERT and ADMINISTER BULK OPERATIONS permissions to run the BULK command.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.