Import XML file into Sql Server 2014 DB

Question

Post reply

Import XML file into Sql Server 2014 DB

ma701ss

Ten Centuries

Points: 1110
More actions
January 17, 2015 at 3:22 pm

#293814

Hi, I've read several articles on importing XML into a SQL DB using different methods but I don't know if they are outdated or if one is preferred over the others. My XML file is as follows:
<?xml version="1.0" ?>
<STOREITEMS>
<CREATED>
<CATEGORY id='181' name='Category name'>
<PRODUCT ITEM='7451'>
<WEIGHT>0.50</WEIGHT>
<NAME>Product name</NAME>
<MODEL>Product model</MODEL>
<PRICE>£5.99</PRICE>
<IMAGE>image.jpg</IMAGE>
<DESCRIPTION>Product description.</DESCRIPTION>
</PRODUCT>
</CATEGORY>
</CREATED>
</STOREITEMS>
Any advice on how best to do this would be appreciated. Also is there any software that will create a schema from an XML file?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 1

In Management Studio right click the database name, chose tasks and then Import Data ...

this will open a wizard that will walk you through the import process

Cheers,

Iulian

ma701ss Ten Centuries Points: 1110 More actions · Answer 2

Thanks that looks useful however there doesn't seem to be an option to import XML files?

Arthur Olcot SSCertifiable Points: 6651 More actions · Answer 3

One option that I often use for ad-hoc imports of XML if you can get the xml files onto the SQL server is this method:

declare @xml xml

SELECT @xml = BulkColumn

FROM OPENROWSET (BULK 'c:\temp\myxmlfile.xml', SINGLE_CLOB) x

another option which I use if I need a regular import of XML files into SQL is by using a FILETABLE as a means to make the data available to SQL. i.e. drop the xml files into the file share exposed by the FILETABLE then you can access that table (files) from within SQL.

Arthur Olcot SSCertifiable Points: 6651 More actions · Answer 4

Also is there any software that will create a schema from an XML file?

You can use visual studio to create a schema from an xml document, and generally it will do a good job, but you'll have to spend a little time afterwards refining it to get it 100% so that it does correctly define the xml with regards to the data types, occurrences and restrictions etc.

ma701ss Ten Centuries Points: 1110 More actions · Answer 5

Thanks for this however I have VS Express and creating an XML schema from an XML document is a feature not included.

Arthur Olcot SSCertifiable Points: 6651 More actions · Answer 6

ok. i just did a quick google and there were a number of free online xsd from xml generators on the first page. not tried any myself so can't comment on how good the resultant schema is. try a couple out and see what you get back and what the differences are. if your xml structure isn't too complicated then I'd imagine most would do a fairly ok job

Tavis Reddick SSC Eights! Points: 817 More actions · Answer 7

Also is there any software that will create a schema from an XML file?

Apart from the previous suggestions and professional XML editing tools, if you have Microsoft InfoPath as part of Microsoft Office, InfoPath can be used to generate (infer) XML schemas from XML sample documents (with some limitations). You can also make edits to the data structure within InfoPath. This might produce a "good enough" approximation schema for your needs.