title: How to create XML file on server using stored procedure or cursor
body: <p>In my database (<code>GoogleSEOData</code>), I have got one Table (GoogleMarkupList) and below sample data are there in table:</p>
  
  <pre><code>PUBLICATION_ID  |   PAGEID  |   URL
  -------------------------------------------------------------
  233             |   654345  |   /english/index.aspx
  345             |   654345  |   /de/english/index.aspx
  432             |   654345  |   /ru/russian/index.aspx
  533             |   654345  |   /ae/arabic/index.aspx
  233             |   452323  |   /english/offers.aspx
  345             |   452323  |   /de/english/offers.aspx
  432             |   452323  |   /ru/russian/offers.aspx
  533             |   452323  |   /ae/arabic/offers.aspx
  233             |   834343  |   /english/destinations.aspx
  345             |   834343  |   /de/english/destinations.aspx
  432             |   834343  |   /ru/russian/destinations.aspx
  533             |   834343  |   /ae/arabic/destinations.aspx
  </code></pre>
  
  <p>Now I want to write SQL Procedure which will take File Path of the server as input say (D://GoogleMarkup) and would create below type of XML files on server (For above sample data).</p>
  
  <p>Name of XML file for 654345 type of data will be <code>654345.XML</code></p>
  
  <pre><code>&lt;ps&gt;
  &lt;p n="233" u="/english/index.aspx" /&gt;
  &lt;p n="345" u="/de/english/index.aspx" /&gt;
  &lt;p n="432" u="/ru/russian/index.aspx" /&gt;
  &lt;p n="533" u="/ae/arabic/index.aspx" /&gt;
  &lt;/ps&gt;
  </code></pre>
  
  <p>Name of XML file for 452323 type of data will be <code>452323.XML</code></p>
  
  <pre><code>&lt;ps&gt;
  &lt;p n="233" u="/english/offers.aspx" /&gt;
  &lt;p n="345" u="/de/english/offers.aspx" /&gt;
  &lt;p n="432" u="/ru/russian/offers.aspx" /&gt;
  &lt;p n="533" u="/ae/arabic/offers.aspx" /&gt;
  &lt;/ps&gt;
  </code></pre>
  
  <p>Name of XML file for 834343 type of data will be <code>834343.XML</code></p>
  
  <pre><code>&lt;ps&gt;
  &lt;p n="233" u="/english/destinations.aspx" /&gt;
  &lt;p n="345" u="/de/english/destinations.aspx" /&gt;
  &lt;p n="432" u="/ru/russian/destinations.aspx" /&gt;
  &lt;p n="533" u="/ae/arabic/destinations.aspx" /&gt;
  &lt;/ps&gt;
  </code></pre>
  
  <p>I know there is utility </p>
  
  <pre><code>SQLCMD -S Server -d Database -E -v pageid=%pid% 
         -Q "Select publication_id as [n], url  from table as [p] where pageid=$(pageid) for xml auto, root('ps')" 
         -o D://GoogleMarkup/%pid%.xml
  </code></pre>
  
  <p>But how to create all the XMLs in one go, so that it will read pageid one by one and would pass to <code>SQLCMD</code> to create XML, do I need to write cursor for that, please suggest with some good code sample.   </p>
  
  <p>Please suggest!!                     </p>