Very Computer

Hi everyone...
Is it possible to format the returned recordset/resultset in SQL. For example,
I've one store procedure/queries as follows:

SELECT DISTINCT TOP 3 mnid, headline FROM news

If I run this query in the Query Analyzer it will return the following resultset:

mnid                        headline
---------------------       ---------------------------------
123                         xxxxxxxxxxxxxxxxxx
345                         xxxxxxxxxerxxxxxxx
678                         xxxxxxxxxxxxxxxxxxxxx

can I format above resultset into the following:

<news id="123" headline="xxxxxxxxxxxx">
<news id="345" headline="xxxxxxxxxxxxxxxxxxxxxxx">
<news id="678" headline="xxxxxxxxxxxxxxx">

Thank You
brdgs
Wai Chong

Wai, something like the following SELECT statement would do the job for you:

SELECT TOP 3  '<news id="' + LTRIM(STR(mnid)) + '" headline="' + headline + '">' [Formatted output] FROM news

But, if you are trying to get the result sets in XML format, SQL Server 2000 has built-in support for that. See SQL Server 2000's Books Online for more information.
--
HTH,
Vyas
http://vyaskn.tripod.com/
SQL Server 7.0 Replication FAQ and commonly encountered problems:
http://vyaskn.tripod.com/repl_ques.htm
Interesting code samples (SQL stored procedures, VB programs):
http://vyaskn.tripod.com/code.htm
Preparing for a DBA/DB Developer interview? Check out the interview questions:
http://vyaskn.tripod.com/iq.htm

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Option RAW - The easiest way to get this is with:

select distinct top 3 mnid, headline from news for xml raw.

Result:

<row mnid="1" headline="Japan confirms royal pregnancy"/>
<row mnid="2" headline="Beijing braces for Olympics 2008 report"/>
<row mnid="3" headline="NIH to issue new cholesterol guidelines"/>

Option EXPLICIT - The difficoult way that produce the required result is:

SELECT 1                    as Tag,
         NULL               as Parent,
         n.mnid as [news!1!mnid],
         n.headline as [news!1!headline]
FROM (select distinct top 3 mnid, headline from news) as n
FOR XML EXPLICIT

Result:

<news mnid="1" headline="Japan confirms royal pregnancy"/>
<news mnid="2" headline="Beijing braces for Olympics 2008 report"/>
<news mnid="3" headline="NIH to issue new cholesterol guidelines"/>

--
Regards,

Thomas
Windows 2000, SQL-Server 2000


Hi everyone...
Is it possible to format the returned recordset/resultset in SQL. For
example,
I've one store procedure/queries as follows:

SELECT DISTINCT TOP 3 mnid, headline FROM news

If I run this query in the Query Analyzer it will return the following
resultset:

mnid                        headline
---------------------       ---------------------------------
123                         xxxxxxxxxxxxxxxxxx
345                         xxxxxxxxxerxxxxxxx
678                         xxxxxxxxxxxxxxxxxxxxx

can I format above resultset into the following:

<news id="123" headline="xxxxxxxxxxxx">
<news id="345" headline="xxxxxxxxxxxxxxxxxxxxxxx">
<news id="678" headline="xxxxxxxxxxxxxxx">

Thank You
brdgs
Wai Chong

Thanks Narayana,
It work fine, but one question is it possible to output the query you give me
into a text file and schedule it to run every 5 minute to execute this query to
produce this text file? I need this, so that my Java Applet can read the data
inside the text file.

Thank You,
brgds
Ong Wai Chong

Quote:-----Original Message-----

Wai, something like the following SELECT statement would do the job for you:

SELECT TOP 3  '<news id="' + LTRIM(STR(mnid)) + '" headline="' + headline + '">'
[Formatted output] FROM news

But, if you are trying to get the result sets in XML format, SQL Server 2000 has
built-in support for that. See SQL Server 2000's Books Online for more
information.
--
HTH,
Vyas
http://vyaskn.tripod.com/
SQL Server 7.0 Replication FAQ and commonly encountered problems:
http://vyaskn.tripod.com/repl_ques.htm
Interesting code samples (SQL stored procedures, VB programs):
http://vyaskn.tripod.com/code.htm
Preparing for a DBA/DB Developer interview? Check out the interview questions:
http://vyaskn.tripod.com/iq.htm

Thanks Narayana,
It work fine, but one question is it possible to output the query you give me
into a text file and schedule it to run every 5 minute to execute this query to
produce this text file? I need this, so that my Java Applet can read the data
inside the text file.

Thank You,
brgds
Ong Wai Chong

Quote:-----Original Message-----

Wai, something like the following SELECT statement would do the job for you:

SELECT TOP 3  '<news id="' + LTRIM(STR(mnid)) + '" headline="' + headline + '">'
[Formatted output] FROM news

But, if you are trying to get the result sets in XML format, SQL Server 2000 has
built-in support for that. See SQL Server 2000's Books Online for more
information.
--
HTH,
Vyas
http://vyaskn.tripod.com/
SQL Server 7.0 Replication FAQ and commonly encountered problems:
http://vyaskn.tripod.com/repl_ques.htm
Interesting code samples (SQL stored procedures, VB programs):
http://vyaskn.tripod.com/code.htm
Preparing for a DBA/DB Developer interview? Check out the interview questions:
http://vyaskn.tripod.com/iq.htm

You can create a DTS job which writes output from the query to a text file.

Or create a SQL Server CmdExec agent job which executes BCP.EXE using QueryOut option
to take query and write output to file.

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

Thanks Narayana,
It work fine, but one question is it possible to output the query you give me
into a text file and schedule it to run every 5 minute to execute this query to
produce this text file? I need this, so that my Java Applet can read the data
inside the text file.

Thank You,
brgds
Ong Wai Chong

Quote:-----Original Message-----

Wai, something like the following SELECT statement would do the job for you:

SELECT TOP 3  '<news id="' + LTRIM(STR(mnid)) + '" headline="' + headline + '">'
[Formatted output] FROM news

But, if you are trying to get the result sets in XML format, SQL Server 2000 has
built-in support for that. See SQL Server 2000's Books Online for more
information.
--
HTH,
Vyas
http://vyaskn.tripod.com/
SQL Server 7.0 Replication FAQ and commonly encountered problems:
http://vyaskn.tripod.com/repl_ques.htm
Interesting code samples (SQL stored procedures, VB programs):
http://vyaskn.tripod.com/code.htm
Preparing for a DBA/DB Developer interview? Check out the interview questions:
http://vyaskn.tripod.com/iq.htm

Thanks for your reply,
I've created a new job (Management -> SQL Server Agent -> Jobs) and did all the
necessary step but I've one question, how to executes BCP.EXE using QueryOut...
can me show me an example? What show I put inside the "Command text box"?

Thank you very much
brgds,
Ong Wai Chong

Here's an example based on a query in the pubs database:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>bcp "select * from pubs..authors"
queryout authors.txt /T /c

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

Thanks for your reply,
I've created a new job (Management -> SQL Server Agent -> Jobs) and did all the
necessary step but I've one question, how to executes BCP.EXE using QueryOut...
can me show me an example? What show I put inside the "Command text box"?

Thank you very much
brgds,
Ong Wai Chong