troubleshooting Question

How to create XML file on server using SQL Procedure/Cursor in SQL Server 2008

Avatar of tia_kamakshi
tia_kamakshiFlag for United Arab Emirates asked on
XMLMicrosoft SQL Server 2008SQL
4 Comments1 Solution912 ViewsLast Modified:
Hi Experts,

In my database (GoogleSEOData), I have got one Table (GoogleMarkupList) and below sample data are there in table:
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

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).

Name of XML file for 654345 type of data will be 654345.XML
<ps>
<p n="233" u="/english/index.aspx" />
<p n="345" u="/de/english/index.aspx" />
<p n="432" u="/ru/russian/index.aspx" />
<p n="533" u="/ae/arabic/index.aspx" />
</ps>
-------------------------------------------------------------
Name of XML file for 452323 type of data will be 452323.XML
<ps>
<p n="233" u="/english/offers.aspx" />
<p n="345" u="/de/english/offers.aspx" />
<p n="432" u="/ru/russian/offers.aspx" />
<p n="533" u="/ae/arabic/offers.aspx" />
</ps>
--------------------------------------------------------------
Name of XML file for 834343 type of data will be 834343.XML
<ps>
<p n="233" u="/english/destinations.aspx" />
<p n="345" u="/de/english/destinations.aspx" />
<p n="432" u="/ru/russian/destinations.aspx" />
<p n="533" u="/ae/arabic/destinations.aspx" />
</ps>
--------------------------------------------------------------

Please suggest SQL query which will do above process.

Thanks.
ASKER CERTIFIED SOLUTION
tia_kamakshi

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros