How generate xml sitemap in SQL Server 2019?

I want to generate a sitemap from within SQL Server and save it to my system drive using the following Product table:

Sample data

loc	image	caption	changefreq	priority
https://example.com/product1	https://example.com/asset/img1.jpg	title1	10	0.1
https://example.com/product2	https://example.com/asset/img2.jpg	title2	5	0

I tried using a FOR XML PATH query but its not giving me the correct result:

DECLARE @xml XML = (SELECT                           (SELECT                                   '' AS "data()"                            FOR XML PATH ('loc'), TYPE),                           (SELECT                                   '' AS "data()"                            FOR XML PATH ('changefreq'), TYPE),                           (SELECT                                   '' AS "data()"                            FOR XML PATH ('priority'), TYPE),                                                             '' AS 'Name!1!ELEMENT'                       FROM dbo.product o                       FOR XML PATH ('url'), ROOT('dummyTag'), TYPE)    -- Magic happens here!               SELECT 1 AS Tag            ,0 AS Parent            ,@xml AS [urlset!1!!xmltext]            ,'http://www.sitemaps.org/schemas/sitemap/0.9' AS [urlset!1!xmlns]            ,'http://www.sitemaps.org/schemas/sitemap/0.9' AS [urlset!1!xmlns:image]      FOR XML EXPLICIT  

---

Template xml:

<urlset xmlns:image="http://www.google.com/schemas/sitemap-image/1.1" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">    <url>      <loc></loc>      <changefreq></changefreq>      <image:image>        <image:loc></image:loc>        <image:caption></image:caption>      </image:image>    </url>  </urlset>  

What I want is a result like this:

<?xml version="1.0" encoding="UTF-8"?>  <urlset      xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"      xmlns:image="http://www.google.com/schemas/sitemap-image/1.1">      <url>          <loc>https://www.example.com/product/dkp-4658551/%D9%85%D8%A7%D9%86%D8%AA%D9%88-%D8%B2%D9%86%D8%A7%D9%86%D9%87-27-%D9%85%D8%AF%D9%84-%DA%AF%D9%84-%D9%88-%D9%BE%D9%86%D8%AC%D8%B1%D9%87-%DA%A9%D8%AF-v78-%D8%B1%D9%86%DA%AF-%D8%B7%D9%88%D8%B3%DB%8C</loc>          <changefreq>weekly</changefreq>          <priority>0.8</priority>          <image:image>              <image:loc>https://example.com/mysite-products/3d1201f33f4e6dd6114ee6ec55be20fb5581f382_1615206830.jpg?x-oss-process=image/resize,m_lfit,h_350,w_350/quality,q_60</image:loc>              <image:caption>مانتو زنانه 27 مدل گل و پنجره کد V78 رنگ طوسی</image:caption>          </image:image>      </url>  </urlset>  

https://stackoverflow.com/questions/66680567/how-generate-xml-sitemap-in-sql-server-2019 March 18, 2021 at 04:13AM