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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tia_kamakshi

ASKER

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