We help IT Professionals succeed at work.

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

906 Views
Last Modified: 2012-06-01
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

Open in new window


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

Open in new window


Please suggest SQL query which will do above process.

Thanks.
Comment
Watch Question

Commented:
Create a Select statment that creates the XML you want
SQLCMD -o parameter to write the contents to the file.

SET pid=834343
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

Just wrote this in the box, completely untried

Author

Commented:
@Banthor...Thanks

I am still not very clear what you trying to say, actually this is first time I am going to do this.

Can you please clear little more or share some links or more code to get it done.

How to create all the xmls in one go, so that will read pageid one by one and would pass to SQLCMD to create XML, do I need to write cursor for that, please suggest with some good code sample.

Thanks.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
As there were no comment from any expert regarding my solution, so considering this as solution

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.