title: iterating XML data in sql server 2005
body: <p>I have a storedprocedure which accepts</p>
  
  <pre><code>@MissingRecordsXML NTEXT
  </code></pre>
  
  <p>It contains XML records. Now someother developer has used a cursor to fetch rows from OPENXML and then has applied all the business rules on each row and after that each row is inserted in a table.</p>
  
  <p>I want to know which is the best way to foreach XML data in SQL Server.I have to enhance performance of this SP as it is really slow when there are multiple records.Cursors are already declared as Read-Only.Please help</p>
  
  <p>Code which fills the cursor from XML is:-</p>
  
  <pre><code>exec sp_xml_preparedocument @hDocSEC OUTPUT,@MissingRecordsXML  
  
  DECLARE SEC_UPDATE CURSOR  FOR  
  SELECT MissingColumn,TableName,PhysicalColName,Grantor_Grantee  
  FROM OPENXML (@hDocSEC,'MissingDS/MissingTable',2)  
  WITH (MissingColumn VARCHAR(1000),TableName VARCHAR(100),  
  PhysicalColName VARCHAR(100),Grantor_Grantee VARCHAR(100) )  
  OPEN SEC_UPDATE  
  FETCH NEXT FROM SEC_UPDATE  
  INTO @MissingColumn,@TableName,@ActualColumnName,@Grantor_Grantee 
  </code></pre>