title: How to use XML string input in SP to delete rows based on two column values
body: <p>I'm trying to delete multiple rows from a table using XML input.  Delete is based on two column values: OwnerKey and QuestionNumber.  (Neither column contains unique values.)</p>
  
  <p>The only way I can figure to make this work is to build a temporary table that contains the XML input values (ie - the 2 columns) and then issue a DELETE based on a JOIN of the temporary table and the target table.  [I'm all ears if someone has a better approach.]</p>
  
  <p>The problem that I'm running into now is that the CREATE PROC fails with an "Invalid Column name" message - pointing to "QuestionNumber" - the second attribute in the XML string.</p>
  
  <p>If I explicitly list both columns in the SELECT INTO statement, I get a syntax error - so I used <strong>SELECT *</strong>.   This doesn't work either.</p>
  
  <p>Can anyone give me an example of how to do this?</p>
  
  <pre><code>XML Input string (formatted here for readability):
  -- &lt;Answers&gt;
  --     &lt;WebUser OwnerKey="104" QuestionNumber="1"/&gt;
  --     &lt;WebUser OwnerKey="104" QuestionNumber="3"/&gt;
  -- &lt;/Answers&gt;
  </code></pre>
  
  <p>SP:</p>
  
  <pre><code>CREATE PROCEDURE WebUserDeleteAnswer 
      @DeleteXMLString text
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @hDoc int
      EXEC sp_xml_preparedocument @hDoc OUTPUT,
          @DeleteXMLString
  
      SELECT * INTO #temp     
  --      OwnerKey                
  --      INTO #temp      
      FROM OPENXML(@hdoc, 'Answers/WebUser', 2)
  
      DELETE FROM WebUser 
      WHERE ( WebUser.OwnerKey = OwnerKey 
          AND WebUser.wu_QuestionNumber = **QuestionNumber**)
  
      DROP TABLE #temp
  
      EXEC sp_xml_removedocument @hDoc
  
  END
  GO
  </code></pre>
  
  <hr>
  
  <p>Figured it out.  Needed to modify the SELECT and DELETE commands:</p>
  
  <p>(Changed <strong>#temp</strong> to <strong>XmlTable</strong> for clarity)</p>
  
  <pre><code>    BEGIN 
      DECLARE @hDoc int
      EXEC sp_xml_preparedocument @hDoc OUTPUT,
          @DeleteXMLString
  
      SELECT * INTO XmlTable  
      FROM OPENXML(@hdoc, 'Answers/WebUser', 2) 
      WITH (OwnerKey int '@OwnerKey',
            QuestionNumber int '@QuestionNumber') 
  
  
      DELETE WebUser
      FROM WebUser
          INNER JOIN XmlTable ON
          (XmlTable.OwnerKey = WebUser.OwnerKey)
          AND (XmlTable.QuestionNumber = WebUser.wu_QuestionNumber)
  
      EXEC sp_xml_removedocument @hDoc
      DROP TABLE XmlTable
  </code></pre>