Post reply

Convert from XML to SQL tables and vice-versa using XSD schemas

  • July 3, 2009 at 6:48 am

    #207881

    I want to compare the performance of running queries against data stored in XML format versus the same data stored in SQL tables. I have XML files of data which I need to convert to SQL tables; I also have SQL tables of data to convert to XML. I am using an XSD schema for the XML data.

    I have read MSDN articles on Creating XML Views by Using Annotated XSD Schemas (http://msdn.microsoft.com/en-us/library/aa258637(SQL.80).aspx) and Performing Bulk Load of XML Data (http://msdn.microsoft.com/en-us/library/aa225763(SQL.80).aspx), which explain the process. What I haven't understood is how to actually write this in SQL code (i.e. T-SQL); I haven't seen any concrete examples of this either. Can anyone advise me where to look for a simple step-by-step tutorial?

    All answers gratefully received,

    Ed Graham

  • July 3, 2009 at 4:33 pm

    #1019885

    Hi,

    you might want to look into the series of articles by Jacob Sebastian, for example this one[/url].

    If you're interested in the rest of the articles please search for "XML Workshop" on this site.

    If there are any questions left please provide sample data (including table def) together with your expected result as per the link in my signature and we'll have a look at it. Please note that you should use [ code="xml" ] tag without blanks for posting xml data.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 13, 2009 at 4:05 am

    #1023764

    Thanks very much for your reply; those articles you suggested were very informative. In the end, I couldn't find a way of doing it in T-SQL but managed to write some C# code to solve the problem:

    static void Main(string[] args)

    {

    try

    {

    SQLXMLBulkLoad4Class objBL = new SQLXMLBulkLoad4Class();

    objBL.ConnectionString = "Provider=sqloledb;server=TURION;database=TempDB;integrated security=SSPI";

    // turn this one off to generate the tables only (loading no data)

    objBL.BulkLoad = true;

    // turn these two off once the tables have been generated (so after the first run)

    objBL.SchemaGen = true;

    objBL.SGUseID = true;

    objBL.CheckConstraints = true;

    objBL.ForceTableLock = true;

    objBL.KeepIdentity = false;

    string filePath = Directory.GetCurrentDirectory() + "\\..\\..\\..\\";

    objBL.ErrorLogFile = filePath + "Errors\\error.xml";

    objBL.Execute(filePath + "Schema\\schema_1.xsd", filePath + "Data\\data_1.xml");

    }

    catch (Exception e)

    {

    Console.WriteLine(e.ToString());

    }

    }

    Thanks again for your help,

    Ed

  • October 11, 2010 at 5:15 am

    #1234900

    What did you find for a performance result??

  • October 15, 2010 at 11:15 pm

    #1237949

    I can't remember, I'm afraid -- so clearly nothing useful!

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

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