Alternative of FOR XML PATH in SQL Server 2000

Question

0.00/5 (No votes)

See more:

SQL-server-2000

Hi All,

My requirement is to write a query in SQL Server 2000. Scenario is:

Suppose a table has two columns ID(not primary key) and Name. Values are like-
ID Name
1 A
1 B
1 C
2 D
2 E

then I have to query this table to get the output like
ID Name
1 A,B,C
2 D,E

I have written a query for the same using FOR XML PATH but that does not supported in SQL Server 2000 but works good in 2005/2008. Also I tried to put some extra logic to get this output but it results in bad performance as the input table is huge. But using FOR XML PATH query on the same table in 2005 it takes to time to output.

So, please let me know what can be the possible replacement of FOR XML PATH in 2000.
I tried XML AUTO but that is giving XML results so please write the query if your suggestion is to use XML AUTO.

Any help is highly appreciated.

Thanks,
Ankit J.

Posted 27-Jan-13 23:35pm

Member 9748847

Add a Solution

Comments

CHill60 28-Jan-13 5:56am

how about FOR XML RAW? PATH isn't supported in 2000

Member 9748847 28-Jan-13 7:39am

@CHil60 Tried XML RAW. Its giving result in the XML format. Something like:
ID NAME
1 <row name="A"><row name="B"><row name="C">
2 <row name="D"><row name="E">

If you think XML RAW will work, can you please write a simple query that will give the required output.

Thanks in advance.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tharaka MTR · Answer 1 · 2013-01-28T02:25:00

Solution 1

See my article below, I have explained most of the possibilities.

Generating and splitting a delimited string column[^]

Posted 28-Jan-13 2:25am

Tharaka MTR

Comments

Member 9748847 29-Jan-13 4:37am

@Tharaka
The solution is correct but there is a huge performance overhead.
For the same no of output recores, using FOR XML PATH in 2005/2008 takes no time while COALESCE taking 23-26 sec.

This time difference is un-affordable and it makes the application to throw exceptions.

I tried with FOR XML AUTO in 2000, it takes no time but the problem in its giving records for the concatenated column in XML format.
Like:
ID NAME
1 <e name="A"><e name="B"><e name="C">
2 <e name="D"><e name="E">

Please suggest.

Tharaka MTR 29-Jan-13 8:12am

Yep, But As far as I know this is the best and easiest way. You have to use some other optimization mechanize to optimize this query. Some experts said that ISNULL is bit faster than COALESCE.

May be I'm wrong. :)