Post reply

XML - SQL

  • November 1, 2013 at 7:50 am

    #282056

    I need to import a load of XML into SQL server. There is no database for this currently. My understanding of this is

    •New database

    •XML file

    •Mapping schema file (basically links the columns with what data type they are)

    Then I can do a BULK import.

    Does anybody know of a tool which does all this for you? In some of the XML files, there are over 400 descriptions which will require their own column.

    Any help would be GREATLY appreciated

  • November 1, 2013 at 8:12 am

    #1663421

    Sounds like an SSIS project to me

  • November 1, 2013 at 9:08 am

    #1663444

    I had no schema as such to do this, but.. if you open excel and open an XML file, it creates the schema for you. Then you can convert the excel into SQL easily.

    Does anybody know of a better method? I have never really used SSIS, if this is the best method I will look into it.

    Doing it the way I did above.. is going to be hard to automate :\

  • November 6, 2013 at 7:00 am

    #1664454

    Still stuck on a solution for this if anybody could assist

  • November 6, 2013 at 2:59 pm

    #1664576

    SQLSteve (11/6/2013)

    Still stuck on a solution for this if anybody could assist

    I'm short on time here but will help get you started. Nothing you are doing sounds too difficult using the tools provided in SQL Server.

    First, to get the xml data into you DB you could use OPENROWSET like this:

    --KEY! When doing the import, replace single quotes with CHAR(39)

    SELECT * FROM OPENROWSET(

    BULK 'G:\xml files\xmldata.txt',

    SINGLE_BLOB) AS x

    The key here is to replace single quotes with CHAR(39)!

    There are two good tools for creating XML schemas that I have used: XMLSpy and Oxygen. You just open up the XML document in either application and you will have the option to create an XML schema for that file. Oxygen is cheap (a few $100 if I remember correctly). XMLSpy is a little more. Each tool will be better than using Excel as they both have more options.

    Update: I originally included code for producing an XML schema with FOR XML RAW, XMLSCHEMA but it will not suffice for what you are doing.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 5 posts - 1 through 4 (of 4 total)

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