0

I need to query my database that has thousands of rows to generate my website's site map. Since it has many rows I will create a siteindex.xml like this:

<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<sitemap>
   <loc>https://www.test.com/sitemap_1.xml</loc>
</sitemap>
<sitemap>
    <loc>https://www.test.com/sitemap_2.xml</loc>
</sitemap>
<sitemap>
    <loc>https://www.test.com/sitemap_3.xml</loc>
</sitemap>
<sitemap>
    <loc>https://www.test.com/sitemap_4.xml</loc>
</sitemap>
</sitemapindex>

With each sitemap xml having the maximum 500 results allowed.

I'm thinking about looping recordsets in PHP and generating the files but is there a best (and faster) way to generate these files directly from SQL Server?

Let's image for example sake that the Table structure is a one column named LINK with a full url

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
Carlos Alves Jorge
  • 1,919
  • 1
  • 13
  • 29
  • 1
    How could we know? We don't even know which DBMS you are using. And we can't see any table structure. Yes it is possible to generate xml from sql. – Sean Lange Sep 28 '17 at 16:49
  • @SeanLange Just edited... I'm using SQL sever. In terms of table structure let's just say it only has one column with the complete URL – Carlos Alves Jorge Sep 28 '17 at 16:57
  • Can you post the table structure and some sample data? Generally speaking site maps need to build the tree structure, not just have a flat dump of pages. Help us help you. – Sean Lange Sep 28 '17 at 17:11

1 Answers1

0

As alternative to looping with PHP you can save the results of query to a text file directly with sqlcmd utility:

sqlcmd -i sitemap_query.sql -o sitemap.txt

and then split the file.

Or even write batch script that perform query with offset until there are no more records. You can pass the variables like follow:

sqlcmd -i sitemap_query.sql -o sitemap.txt -v varOFFSET='500'

But I really not sure that performance difference with PHP looping will be critical.

Oleh Rybalchenko
  • 6,998
  • 3
  • 22
  • 36