title: Best approach, Dynamic OpenXML in T-SQL
body: <p>i'm storing XML values to an entry in my database. Originally, i extract the xml datatype to my business logic then fill the XML data into a DataSet. I want to improve this process by loading the XML right into the T-SQL. Instead of getting the xml as string then converting it on the BL.</p>
  
  <p>My issue is this: each xml entry is dynamic, meaning it can be any column created by the user. I tried using this approach, but it's giving me an error:</p>
  
  <pre><code>    CREATE PROCEDURE spXMLtoDataSet
      @id uniqueidentifier,
      @columns varchar(max)
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      DECLARE @name varchar(300);
      DECLARE @i int;
      DECLARE @xmlData xml;
      (SELECT @xmlData = data, @name = name FROM XmlTABLES WHERE (tableID = ISNULL(@id, tableID)));
  
      EXEC sp_xml_preparedocument @i OUTPUT, @xmlData
  
      DECLARE @tag varchar(1000);
      SET @tag = '/NewDataSet/' + @name;
      DECLARE @statement varchar(max)
      SET @statement = 'SELECT * FROM OpenXML(@i, @tag, 2) WITH (' + @columns + ')';
      EXEC (@statement);
  
      EXEC sp_xml_removedocument @i
  END
  </code></pre>
  
  <p>where i pass a dynamically written @columns.</p>
  
  <p>For example:</p>
  
  <pre><code>spXMLtoDataSet 'bda32dd7-0439-4f97-bc96-50cdacbb1518', 'ID int, TypeOfAccident int, Major bit, Number_of_Persons int, Notes varchar(max)'
  </code></pre>
  
  <p>but it kept on throwing me this exception:</p>
  
  <pre><code>Msg 137, Level 15, State 2, Line 1
  Must declare the scalar variable "@i".
  Msg 319, Level 15, State 1, Line 1
  Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
  </code></pre>