How to return unrelated data for XML
-
Example:
Create table TopLevel
(
TopLevelIDintPrimary Key Identity(1,1),
TopLevelNamevarchar(30) Not Null
)
go
Create table Level2
(
Level2ID intPrimary Key Identity(1,1),
TopLevelIDintnot null,
Level2Namevarchar(30)Not Null,
)
go
Create table Level2A
(
Level2AIDintPrimary Key Identity(1,1),
TopLevelIDintNot Null,
Level2Namevarchar(30)Not Null,
IsActivebitNot Null
)
ALTER TABLE Level2 ADD
CONSTRAINT [FK_Level2_] FOREIGN KEY
(
[TopLevelID]
) REFERENCES [TopLevel] (
[TopLevelID]
)
GO
ALTER TABLE Level2A ADD
CONSTRAINT [FK_Level2A_TopLevelID] FOREIGN KEY
(
[TopLevelID]
) REFERENCES [TopLevel] (
[TopLevelID]
Insert TopLevel
values
('TopLevel1')
go
Insert TopLevel
values
('TopLevel2')
go
Insert Level2
values
(1,'ABC')
go
Insert Level2
values
(1,'XYZ')
go
Insert Level2A
values
(1,'DEF',1)
go
Insert Level2A
values
(1,'TRW',0)
I need to return data in a XML format
with both Level2 and Level2A underneath the Top Level. Level2 adn Level2A are both related to the Top Level both there is no realtion between Level2 and Level2A.
thanks for your help.
DanielP
-
Needs a bit more work
SELECT 1 as Tag,
NULL as Parent,
TopLevel.TopLevelID as [TopLevel!1!TopLevelID],
NULL as [Level2ID!2!Level2ID],
NULL as [Level2ID!2!Level2name]
FROM TopLevel
UNION ALL
SELECT 2,
1,
Level22.TopLevelID,
Level22.Level2ID,
Level22.Level2name
From (
Select t1.TopLevelID,t1.TopLevelName,l2.Level2ID,l2.Level2Name
From TopLevel t1 inner join Level2 l2 On t1.TopLevelID=l2.TopLevelID
Union ALL
Select t1.TopLevelID,t1.TopLevelName,l2.Level2AID as 'Level2ID',l2.Level2Name
From TopLevel t1
Inner Join Level2A l2
On t1.TopLevelID=l2.TopLevelID ) as Level22(TopLevelID,TopLevelName,Level2ID,Level2Name)
Inner Join TopLevel On TopLevel.TopLevelID=Level22.TopLevelID
FOR XML Explicit
<TopLevel TopLevelID="1"/>
<TopLevel TopLevelID="2">
<Level2ID Level2ID="1" Level2name="ABC"/>
<Level2ID Level2ID="2" Level2name="XYZ"/>
<Level2ID Level2ID="1" Level2name="DEF"/>
<Level2ID Level2ID="2" Level2name="TRW"/>
</TopLevel>
-
Thank you for your help. I did try it, but it won't work for our solution. The actual XML doc that I am trying to build would have different number of fields for the unrelated table and with the UNION ALL for Explicit statement it takes the names of the top union. So for example, the Level2A columns would have the Level2 column names but the correct values. But if Level2A returns additional columns that is greater than the number of columns that table Level2 has, it won't work either.
At this time a developer is working on just building the xml from a combanations of Strings and querying the database through C+
Ex.
<Regions>
<Region Name="Region1" ImageKey="" Text="Sample"
Type="Application">
<Region Name="Commands">
<Command CommandName="Test1" />
<Command CommandName="Test2" />
<Command CommandName="Test3" />
<Command CommandName="Test4" />
</Region>
<Region MenuItemName="Menus">
<MenuItem MenuItemName="Menu1" />
<MenuItem MenuItemName="Menu2" />
<MenuItem MenuItemName="Menu3" />
<MenuItem MenuItemName="Menu4" />
</Region>
</Region>
</Regions>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply