Avatar of tia_kamakshi
tia_kamakshi
Flag for United Arab Emirates asked on

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

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.
XMLMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
tia_kamakshi

8/22/2022 - Mon
Banthor

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
tia_kamakshi

ASKER
@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.
ASKER CERTIFIED SOLUTION
tia_kamakshi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
tia_kamakshi

ASKER
As there were no comment from any expert regarding my solution, so considering this as solution
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck