xml path in SQL 2000

anushahanna
anushahanna used Ask the Experts™
on
how could we convert the foll code for SQL 2000?.

thanks
----------
create table sp_dep (name varchar (1000), type varchar(100), baseobject sysname null)

exec sp_MSforeachtable 'insert sp_dep (name,type) EXEC sp_depends ''?'';

update sp_dep
set baseobject=''?'' where baseobject is null'

select t1.baseobject, t1.type, count(*),
 (select t2.name + ',' from sp_dep t2 where t2.baseobject = t1.baseobject and t1.type = t2.type
for xml path('')) as dependencies
 from sp_dep t1
 group by t1.baseobject, t1.type
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
create the function attached and use it in your query like this:
select t1.baseobject, t1.type, count(*),
 dbo.fn_List_str(baseobject, type) as dependencies
 from sp_dep t1
 group by t1.baseobject, t1.type
 

CREATE FUNCTION [dbo].[fn_List_str](@baseobject int, @type int) RETURNS nvarchar(2000) AS
BEGIN
DECLARE @listStr nvarchar(2000)
SELECT @listStr = COALESCE(@listStr+',' ,'') + [name] FROM sp_dep WHERE baseobject = @baseobject and type = @type
 RETURN @listStr
 END

Open in new window

btw, adjust the @baseobject and @type datatype to the one of your current table. I've used int to declare the function parameters, but you should use the same as your columns.

Author

Commented:
ralmada, where will we call sp_depends system function, in this routine?
same way you've called before. Only part that needs to be changed is the for xml part. So to clarify
1) You create the function (attached below)
2) Then you run the following code:
create table sp_dep (name varchar (1000), type varchar(100), baseobject sysname null)

exec sp_MSforeachtable 'insert sp_dep (name,type) EXEC sp_depends ''?'';

update sp_dep
set baseobject=''?'' where baseobject is null'

select t1.baseobject, t1.type, count(*),
dbo.fn_List_str(baseobject, type) as dependencies
from sp_dep t1
group by t1.baseobject, t1.type

CREATE FUNCTION [dbo].[fn_List_str](@baseobject sysname, @type varchar(100)) RETURNS nvarchar(2000) AS
BEGIN
DECLARE @listStr nvarchar(2000)
SELECT @listStr = COALESCE(@listStr+',' ,'') + [name] FROM sp_dep WHERE baseobject = @baseobject and type = @type
 RETURN @listStr
 END

Open in new window

Author

Commented:
I got you.. thanks very much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial