FOR XML EXPLICIT issue

Hi guys:
I'm in an urgent need to know if we can dynamically alter values in
querying tables using FOR XML EXPLICIT. In the following sample, I have
3 tabels involved vis_Rule, vis_IF, and vis_AND.
Table Structure:
Table: vis_Rule
Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
Table: vis_IF
Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
Table: vis_AND
Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
In the following stored proc, can we grab the RuleId of the first
SELECT statement and pass it to the next SELECT statement such that it
extracts only the desired rows? In other words, can we dynamically
assign values from one part of the SELECT statement to the next?
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id],
NULL as [compare!4!id],
NULL as [compare!4!operator]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL,
NULL,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
NULL,
NULL,
NULL,
NULL,
if.IFId,
[and].AndId,
NULL,
NULL
FROM vis_AND [and], vis_IF if
where [and].IFId = if.IFId
FOR XML EXPLICIT
GO
Thanks in advance.My apologies for the late reply, but I was on vacation the last couple of
ws.
I am not quite sure what you try to acheive. Do you want to only provide the
nesting of the tree for a given ruleID?
In that case try:
CREATE Table vis_Rule (RuleId int,Name nvarchar(40), Priority int, Active
bit)
go
insert into vis_Rule values (1, N'r1', 1, 1)
insert into vis_Rule values (2, N'r2', 2, 1)
go
CREATE Table vis_IF (IFId int, RuleId int --> Foreign Key to RuleId in
vis_Rule
)
go
insert into vis_IF values (1, 1)
insert into vis_IF values (2, 1)
insert into vis_IF values (3, 1)
insert into vis_IF values (4, 2)
go
CREATE Table vis_AND (AndId int, IFId int --> Foreign Key to IFId in vis_IF
)
insert into vis_AND values (1, 1)
insert into vis_AND values (2, 1)
insert into vis_AND values (3, 2)
insert into vis_AND values (4, 2)
insert into vis_AND values (5, 3)
insert into vis_AND values (6, 4)
go
declare @.rid int
set @.rid = 1
SELECT TOP 1
1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
where r.RuleId = @.rid
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = @.rid
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
[if].RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if]
where [and].IFId = [if].IFId
AND [if].RuleId = @.rid
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
If you want it for all rules, try:
SELECT 1 AS Tag,
NULL AS Parent,
r.RuleId as [rule!1!Id],
r.Name as [rule!1!name],
r.Priority as [rule!1!priority],
r.Active as [rule!1!active],
NULL as [if!2!id],
NULL as [and!3!id]
FROM vis_Rule r
UNION ALL
SELECT 2 AS Tag,
1 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
NULL
FROM vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
r.RuleId,
NULL,
NULL,
NULL,
[if].IFId,
[and].AndId
FROM vis_AND [and], vis_IF [if], vis_Rule r
WHERE [if].RuleId = r.RuleId
AND r.RuleId = [if].RuleId
AND [and].IFId = [if].IFId
ORDER BY [rule!1!Id], [if!2!id], Parent
FOR XML EXPLICIT
Best regards
Michael
<shamod@.gmail.com> wrote in message
news:1122527565.227837.240190@.g43g2000cwa.googlegroups.com...
> Hi guys:
> I'm in an urgent need to know if we can dynamically alter values in
> querying tables using FOR XML EXPLICIT. In the following sample, I have
> 3 tabels involved vis_Rule, vis_IF, and vis_AND.
> Table Structure:
> Table: vis_Rule
> Column1: RuleId, Column2: Name, Column3: Priority, Column4: Active
> Table: vis_IF
> Column1: IFId, Column2: RuleId -> Foreign Key to RuleId in vis_Rule
> Table: vis_AND
> Column1: AndId, Column2: IFId -> Foreign Key to IFId in vis_IF
> ++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++
> In the following stored proc, can we grab the RuleId of the first
> SELECT statement and pass it to the next SELECT statement such that it
> extracts only the desired rows? In other words, can we dynamically
> assign values from one part of the SELECT statement to the next?
> SELECT TOP 1
> 1 AS Tag,
> NULL AS Parent,
> r.RuleId as [rule!1!Id],
> r.Name as [rule!1!name],
> r.Priority as [rule!1!priority],
> r.Active as [rule!1!active],
> NULL as [if!2!id],
> NULL as [and!3!id],
> NULL as [compare!4!id],
> NULL as [compare!4!operator]
> FROM vis_Rule r
> UNION ALL
> SELECT 2 AS Tag,
> 1 AS Parent,
> r.RuleId,
> NULL,
> NULL,
> NULL,
> [if].IFId,
> NULL,
> NULL,
> NULL
> FROM vis_IF [if], vis_Rule r
> WHERE [if].RuleId = r.RuleId
> UNION ALL
> SELECT 3 AS Tag,
> 2 AS Parent,
> NULL,
> NULL,
> NULL,
> NULL,
> if.IFId,
> [and].AndId,
> NULL,
> NULL
> FROM vis_AND [and], vis_IF if
> where [and].IFId = if.IFId
> FOR XML EXPLICIT
> GO
> Thanks in advance.
>