Import XML file into Sql Server 2014 DB

  • ma701ss

    Ten Centuries

    Points: 1110

    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?

  • Iulian -207023

    SSCertifiable

    Points: 7509

    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

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

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    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: 6008

    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

    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: 6008

    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

    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.

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

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