title: Importing and validating XML file using SSIS or just plain T-SQL?
body: <p>What is the best practice when importing and validating an XML file to a single table (flattened) in SQL Server ?</p>
  
  <p>I've a XML file which contains about 15 complex types which are all related to a single parent element.
  The SSIS design could look like this:
  <img src="https://i.stack.imgur.com/0YQ46.png" alt="SSIS">
  But it's getting very complicated with all those (15) joins.</p>
  
  <p>Is it maybe a better idea to just write T-SQL code to :<br/>
  1) Import the XML into a column which is of the type XML and is linked to a XSD-schema.<br/>
  2) Use this code:<br/></p>
  
  <pre><code>TRUNCATE TABLE XML_Import
  INSERT INTO XML_Import(ImportDateTime, XmlData)
  SELECT GETDATE(), XmlData 
  FROM
  (
      SELECT  * 
      FROM    OPENROWSET (BULK 'c:\XML-Data.xml', SINGLE_BLOB) AS XMLDATA
  ) AS FileImport (XMLDATA)
  
  delete from dbo.UserFlat
  INSERT INTO dbo.UserFlat
  SELECT
      user.value('(UserIdentifier)', 'varchar(8)') as UserIdentifier,
      user.value('(Emailaddress)', 'varchar(70)') as Emailaddress,
      businessaddress.value('(Fax)', 'varchar(70)') as Fax,
      employment.value('(EmploymentData)', 'varchar(8)') as EmploymentData,
      -- More values here ...
  FROM  
      XML_Import CROSS APPLY
      XmlData.nodes('//user') AS User(user) CROSS APPLY
      user.nodes('BusinessAddress') AS BusinessAddress(businessaddress) CROSS APPLY
      user.nodes('Employment') AS Employment(employment)
      -- More 'joins' here ...
  </code></pre>
  
  <p>to fill the 'UserFlat' table ?<br/>Some disadvantages are that you have to manually type the SQLcode, but the advantage here is that I have more direct control how the elements are processed and converted. But I don't know if there are any performance differences between processing XML in SSIS and processing the XML with T-SQL XML statements.</p>
  
  <p><br/>
  Note that some other requirements are:</p>
  
  <ol>
  <li>Error handling : in case of an error, an email must be send to a person.</li>
  <li>Able to process multiple input files with a specific file name pattern : XML_{date}_{time}.xml</li>
  <li>Move the processed XML files to a different folder.</li>
  </ol>
  
  <p>Please advice.</p>