| К Р И Т И К И З А Т Е М А Т А |
| Alex |
Posted - 23/01/2014 : 14:01:05
Со следната скрипта преку користење на CTE и UDF може да се добие добро структуриран XML документ со неограничен број на нивоа во длабочина.
DROP TABLE fixrekurzija -- dokolku postoi
GO
CREATE TABLE [dbo].[Meni](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](500) NOT NULL,
[PID] [int] NULL,
[LNG] [int] NULL,
[Visibility] [bit] NULL,
[Locked] [bit] NULL,
[OrderMeni] [int] NULL,
[Description] [nvarchar](4000) NULL,
[Keywords] [nvarchar](4000) NULL,
[Rewrite] [nvarchar](4000) NULL,
CONSTRAINT [PK_Meni] PRIMARY KEY CLUSTERED
(
-- od ovaa tabela ke se vlecat podatoci i konvertiraat vo XML
GO
-- CTE query
WITH Rekurzija (PID, ID, Name,Rewrite, Level)
AS
(
SELECT e.PID, e.ID, e.Name,e.Rewrite, 0 AS Level
FROM dbo.meni e
WHERE e.PID IS NULL
UNION ALL
SELECT e.PID, e.ID, e.Name,e.rewrite,Level + 1
FROM dbo.meni AS e
INNER JOIN rekurzija AS d
ON e.PID = d.ID
)
SELECT PID, ID, Name,Rewrite, Level
INTO fixrekurzija
FROM rekurzija
-- UDF za rekurziven XML
CREATE function SelectChild(@key as int)
returns xml
begin
return (
select
ID as "@id",
name as "@Title",
Rewrite as "@url",
dbo.SelectChild(ID)
from fixrekurzija
where PID = @key
for xml path('link'), type
)
end
SELECT
Id AS "@id",
Name as "@Title",
rewrite as "@url",
dbo.SelectChild(ID)
FROM fixrekurzija
WHERE PId is null
FOR XML PATH ('link'),root('meni')
ALex |
|
|
