Import xml file into sql database

Sometimes, the XML data may be in a different encoding. If it is, you have to use care during data loading. For example:. If the encoding is not Unicode and is implicit, because of the source code page, the string code page in the database should be the same as or compatible with the code points that you want to load. If no such server code page exists, you have to add an explicit XML declaration with the correct encoding. To use an explicit encoding, use either the varbinary type, which has no interaction with code pages, or use a string type of the appropriate code page.

Then, assign the data to an XML column, variable, or parameter. You should gain basic knowledge of SSIS packages before proceeding with this article. Launch the Visual Studio and create an integration service project. You should choose an appropriate project name and SSIS package. It is not a requirement but a best practice to recognize projects with their names. Right-click on it and rename the data flow task. As you know, we have a source data in the XML format.

SSIS adds the task in the data flow task created earlier. It is the appropriate option for us because of our source data in an XML file.

We do not have an XSD file in my demo. You can specify a separate location as well, if required. We have recently decided to import data in the form of XML, and your reference is perfectly matching our requirement.

I'm unbale to featch the data from xml file into relational table , kindly help me. Given below my XMl File and query. Thank you Arshad, I don't usually comment on these things, but your explanation was awesome!

Your examples were easy to comprehend and adapt to my needs. Fabulous job. XML parsing: line , character , illegal xml character. Very good article Arshad. I played little bit - really helpful for my upcoming project.

Thanks -- Rajesh. Related Articles. Popular Articles. Rolling up multiple rows into a single row and column for SQL Server data. How to tell what SQL Server versions you are running. Resolving could not open a connection to SQL Server errors. Ways to compare and find differences for SQL Server tables and data.

Searching and finding a string value in all columns in a SQL Server table. View all my tips. Challenge is the xml tag name is same at multiple places. See items marked in Bold. When I use your suggested method, I get the values for the repeated xml tags as the first xml tag value. Can you please help me modify my select query with the correct Xpath so that It will return the element marked in bold from the XML doc??

Back To Top I have a similar problem, I have several XML files in a folder, but when reading these, I only read the last file I have, be it 2, 5 or more. It will always read the latest file. This is my Query. XMLFilesTable --delete from dbo. Openxml works fine for what I am trying to do but I would always like to use the preferred way of doing things Should I rewrite my queries? Or is openxml good enough for the job? Sorry for the general question, I just want my code to be as best as it can be.

Thanks for your time. Getting the following error while trying to execute the query. Hi Arshard, Thank you for your sharing! Looking forward to hearing from you and thank you in advance! Simon Mallett The solution for your problem is to write 'text ' instead of '..

Hi Arshad, Thanks for the tip, its really helpful. It creates a nice empty table with all the columns as specified in my select statement. Hi Arshad, I have resolved previous artical issue. Please help Anil Panwar. Hi Arshad , Thanks for writing so helpfull article. Hello everyone, I have a little problem with the code. Thanks a lot! Life Saver! Thanks again! Hi Harry, check to see if the SQL Server service account has access to the folder you are trying to use.

Hi Dan, this is probably a permissions error. Hi Arshad, Thank you for this articole. Good day! Now THAT was brilliant!!!! Hiiii I have lot of XML file. Best Regards Mahender Singh. Thanks Regards. Currently I am in Grand parent level. Viewed 84k times. Thanks, pugu. Improve this question. How do you want the data to be composed once you have imported it? I was hoping to get the data into a single table, accepting that in some cases there would be 'null' entries, for example if one of the Parameters wasn't present.

Add a comment. Active Oldest Votes. Improve this answer. That works great thanks. The problem with this method is that it doesn't include any of the parameters such as "The Muppets" Off to read Books Online! Show 5 more comments. I get the error Cannot create Active X Object but related link about it doesn't seem to help.

Any ideas how to fix it?