title: Convert comma separated string into XML with sequence number in SQL Server 2005
body: <p>I am using SQL Server 2005. I have the following:</p>
  
  <pre><code>DECLARE @list NVARCHAR(MAX)
  DECLARE @xml XML
  
  SET @list = 'aa,bb,cc,dd,ee'
  </code></pre>
  
  <p>I would like to fill in @xml with data from the @list with the following format:</p>
  
  <pre><code>&lt;row seq="1" col1="aa" /&gt;
  &lt;row seq="2" col1="bb" /&gt;
  &lt;row seq="3" col1="cc" /&gt;
  &lt;row seq="4" col1="dd" /&gt;
  </code></pre>
  
  <p>I tried to convert the @list into an XML string with the following:</p>
  
  <pre><code>SET @xml = '&lt;row&gt;' + REPLACE(@list, ',', '&lt;/row&gt;&lt;row&gt;') + '&lt;/row&gt;'
  </code></pre>
  
  <p>and then tried to look for a FLWOR Expression in the @xml.query method that can add the sequence number for the rows, but I am out of luck as <strong>there is no  let clause in SQL server 2005</strong>. Could anyone give me a hint/workaround? Thanks.</p>