title: XML Transformation and SQL Performance
body: <p>I have a scenario where I got 100+ procedures written, these are doing the following tasks</p>
  
  <ol>
  <li>loading XML files </li>
  <li>Manipulating with data that come from XML</li>
  <li>store or update data into the tables</li>
  </ol>
  
  <p>All of those procedures contains temporary tables. Which affect the SQL Server performance.Currently all of procedures are running by scheduled jobs. And that makes server busy. I'm searching for some optimized way to perform transformation so that it won't effect the SQL performance. I don't have a much experience to do it. is there any clue how to make it happen.</p>
  
  <p>here is the code how I'm getting XML data</p>
  
  <pre><code>CREATE TABLE #XMLWithOpenXML([Id] [int] IDENTITY(1,1) NOT NULL, [XMLData] [xml] NULL)
  CREATE TABLE #AllFiles  (Id INT IDENTITY, Subdirectory NVARCHAR(100), Depth INT, [File] INT)
  CREATE TABLE #RowDetail (colone INT, ColTwo NVARCHAR(50), ColThree NVARCHAR(50), )
  
  INSERT INTO #AllFiles 
  EXEC xp_dirtree 'C:\XmlFiles',1,1
  DELETE FROM #AllFiles  WHERE [file] = 0
  DECLARE @Count AS INT, @FileName AS NVARCHAR(100), @Index AS INT, @Query AS NVARCHAR(4000)
  SET @Index = 1
  SELECT @Count = COUNT(*) FROM #AllFiles 
  WHILE(@Index &lt;= @Count)
  BEGIN
      SELECT @FileName = Subdirectory FROM #AllFiles  WHERE Id = @Index
      SET @Query = 
      'INSERT INTO #XMLWithOpenXML (XMLData)
      SELECT CONVERT(XML, BulkColumn) AS BulkColumn 
      FROM OPENROWSET(BULK ''C:\XmlFiles\' + @FileName + ''', SINGLE_BLOB) AS x;'
      EXEC(@Query)
      SET @Index = @Index + 1
  END
  
  DECLARE @XML AS XML, @hDoc AS INT
  SET @Index = 1
  SELECT @Count = COUNT(*) FROM #XMLWithOpenXML 
  WHILE(@Index &lt;= @Count)
  BEGIN
      SET @XML = NULL
      SELECT @XML = XMLData FROM #XMLWithOpenXML  WHERE Id = @Index
      EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
  
      INSERT INTO #RowDetail
      SELECT  x.colone,
              x.ColTwo,
              x.ColThree
      FROM OPENXML(@hDoc, '/root/dir/subdir')
      WITH 
      (   
          colone INT '../@Id',
          ColTwo NVARCHAR(50) '@name',
          ColThree NVARCHAR(50) '@value'
      ) AS x
  
      IF(@hDoc IS NOT NULL)
          EXEC sp_xml_removedocument @hDoc
  
      SET @Index = @Index + 1
  END
  </code></pre>
  
  <p>I'm tackling with different XML formats. Each format contains many XML files. I have to load all of those accordingly along with optimized way.</p>