title: Looping through SQL OpenXML to get Field Names
body: <p>I have a stored procedure that will receive an xml document I will be processing.  Depending on the different processes that will call this procedure, I will be inserting data into a temp table from this openxml prepared document that will be from one group or another.</p>
  
  <p>Ex:  It could be:</p>
  
  <pre><code>FROM OPENXML(@idoc, '/data/BeneInfoGroup/BeneInfo', 1)
  </code></pre>
  
  <p>OR it could be:</p>
  
  <pre><code> FROM OPENXML(@idoc, '/data/PersonalInfoGroup/PersonalInfo', 1)
  </code></pre>
  
  <p>Only one group will come through.  Depending on this group - I want to create an SQL string (to be EXECUTED) that inserts into a temp table (already created) and contains field names from the openxml doc.</p>
  
  <p>So my code (in the 2 examples above) could look like:</p>
  
  <pre><code>        strSQL = N'INSERT INTO @tmpTbl (BeneID, BeneSSN, BeneDate)
          SELECT BeneID, BeneSSN, BeneDate
          FROM OPENXML(@idoc, ''/data/BeneInfoGroup/BeneInfo'', 1) 
  WITH (BeneID nvarchar(50) ''BeneID'', BeneSSN nvarchar(50) ''BeneSSN'', BeneDate nvarchar(50) ''BeneDate'')'
  </code></pre>
  
  <p>Or it could look like:</p>
  
  <pre><code>         strSQL = N'INSERT INTO @tmpTbl (PersonID, PersonSSN, PersonDate)
           SELECT PersonID, PersonSSN, PersonDate
           FROM OPENXML(@idoc, ''/data/PersonInfoGroup/PersonInfo'', 1) 
  WITH (PersonID nvarchar(50) ''PersonID'', PersonSSN nvarchar(50) ''PersonSSN'', PersonDate nvarchar(50) ''PersonDate'')'
  </code></pre>
  
  <p>The 2 examples above are minimal.  It won't be "Person" vs "Bene" headers.  The fields could be named anything in either group.  So I'd like to loop through the openxml document and have the fieldnames go into my string where appropriate.  I can take care of the data type details, etc.  I just need to pull the field names from the openxml doc I've prepared.</p>
  
  <p>I do NOT need an alternative method using something other than OPENXML, please.
  The basic question is - how do I pull the field name from the document in a way that's not hard coded depending on the xml doc I receive?</p>
  
  <p>I don't want to create a table of the field names.
  I just want to go through a loop and reference every fieldname that exists for that particular group in my openxml.</p>
  
  <p>No need to worry about @tmpTbl - I have created this with all possible fields that might be inserted into it.</p>
  
  <p>Thanks in advance!  Daniel</p>