title: Sql server - Limiting the number of elements returned using select FOR XML PATH command
body: <p>Using sql server with FOR XML PATH</p>
  
  <p>Say my XML output is this:</p>
  
  <pre><code>&lt;urlset&gt;
    &lt;url&gt;
      &lt;loc&gt;abc&lt;/loc&gt;
      &lt;lastmod&gt;efg&lt;/lastmod&gt;
      &lt;changefreq&gt;hij&lt;/changefreq&gt;
      &lt;priority&gt;kml&lt;/priority&gt;
    &lt;/url&gt;
  &lt;/urlset&gt;
  </code></pre>
  
  <p>And I want to have no more than 40,000 rows (row = "url" element) </p>
  
  <p>How can I accomplish this ?</p>
  
  <p>This is my sql :</p>
  
  <pre><code>SELECT (SELECT p.documentUrl
                 + CONVERT(NVARCHAR(20), sm.txtId)        AS 'loc',
                 CONVERT(NVARCHAR(10), m.changedate, 103) AS 'lastmod',
                 'daily'                                  AS 'changefreq',
                 '0.8'                                    AS 'priority'
          FROM   cms_sitesmaps sm
                 INNER JOIN cms_main m
                         ON sm.txtid = m.txtid
                 INNER JOIN cms_Packs p
                         ON sm.productcode = p.packCode
          WHERE  IsDeleted = 0
                 AND SiteMapName = @SiteMapName
                 AND sm.ProductCode = @ProductCode
          FOR XML PATH('url'), TYPE, ELEMENTS, ROOT('urlset')) AS 'sitemapxml' 
  </code></pre>
  
  <p>Thank you</p>