SQL Server Performance

XML data into sql server 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by kondap, Jan 19, 2009.

  1. kondap New Member

    Hi
    I am using sql server 2005 with Windows XP. I have a question on how to load xml data into sql server by applying business logic dynamically.
    Question: The input is XML file which will have 5000 records. Before loading data into sql server destination tables , I have to apply business logic (2-3 level). what is the best way to achieve this?
    Would it be feasible to write a stored proc with business logic and call this proc for each record in xml (5k), its a performance overhead I believe.
    what is the best way to achieve this dynamically
    I am assuming to follow below approach.
    1. read the xml using SSIS and store in sql server staging table
    2. read each record and pass it to stored proc (Stored proc will check for the business logic and inserts into multiple tables by checking referential integrity based on the logic).
    Appreciate your help
    Thanks
    PK
  2. RedDevils New Member

    Its always a best practices to write your Business logic in stored proc, so if any changes occur (in future) logic the only thing is you have to update the SP.
    also it will not create any performance overhead. Besides, for your XML data manipulation it would be good to write the logic with in the XP , as you can use XML datatype as well as stored procs for XML manipulation.
    -Abhijit, MCP
  3. bhushanvinay New Member

    Kind of disagree with the business logic in the stored proc. a little logic for filtering is fine but not have the comple business logic. you should always have a business object which do's this work for you.
  4. satya Moderator

Share This Page