April 15, 2009 at 10:41 am
I have three tables (please see attached file for the structure). How do I generate an XML in hierarchical manner using FOR XML PATH? The result should be base from the Roles table to the Mapping table.
The hierarchy should follow in this order Region/District/Store. One store role can be mapped to many Regions/Districts/Stores directly or indirectly. If a certain store already belongs to a certain Region or District from a certain mapping and is mapped directly from a user role then it should not be included in the display.
Sample display would be:
<Stores>
<Region Description="Region One" Value="3" CompanyStoreID="3">
<District CompanyID="991" StoreID="99999" Description="District One" >
<Store CompanyID="111" StoreID="12345" Description="Store1" />
<Store CompanyID="222" StoreID="67890" Description="Store2" />
</District>
<District CompanyID="992" StoreID="99999" Description="District Two">
<Store CompanyID="333" StoreID="12345" Description="Store3" />
</District>
</Region>
</Stores>
Or can be in this kind of mapping result:
<Stores>
<Region Description="Region One" Value="3" CompanyStoreID="3">
<District CompanyID="991" StoreID="99999" Description="District One" >
<Store CompanyID="111" StoreID="12345" Description="Store1" />
<Store CompanyID="222" StoreID="67890" Description="Store2" />
</District>
<District CompanyID="992" StoreID="99999" Description="District Two">
<Store CompanyID="333" StoreID="12345" Description="Store3" />
</District>
</Region>
<District CompanyID="883" StoreID="88888" Description="District Four">
<Store CompanyID="444" StoreID="67890" Description="Store4" />
</District>
<Store CompanyID="555" StoreID="42133" Description="Store5" />
</Stores>
* Meaning that a certain StoreRole has been mapped directly to certain District (Four) that doesn't belong to Region One and a Store (5) that doesn't belong to Region One and District Four.
Any thoughts and sample working code are highly appreciated. Thank you for your time.
April 15, 2009 at 1:51 pm
Provide the following:
1. object creation scripts
2. sample data
April 15, 2009 at 1:54 pm
I would need to see what the tables look like before I can answer this. It's generally a pretty simple thing to answer, once I know what the tables look like. Best way to do that is to post the create scripts for them. Is that something you can do?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 15, 2009 at 2:18 pm
Please have a look to the link in my signature for explanation how to provide some sample DDL and data.
Thanks
Flo
April 16, 2009 at 10:41 am
Please see code below for the object create scripts and sample data, sorry bout this:
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Definition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Definition](
[StoreLevelID] [smallint] NOT NULL,
[StoreLevelName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Definition] PRIMARY KEY CLUSTERED
(
[StoreLevelID] ASC
)
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mapping]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Mapping](
[StoreRoleMappingID] [int] NOT NULL,
[StoreLevelID] [smallint] NOT NULL,
[ParentKey] [int] NULL,
[Value] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CompanyID] [int] NULL,
[StoreID] [int] NULL,
CONSTRAINT [PK_Mapping] PRIMARY KEY CLUSTERED
(
[StoreRoleMappingID] ASC
),
CONSTRAINT [FK_Mapping_Definition] FOREIGN KEY
(
[StoreLevelID]
)
REFERENCES [dbo].[Definition]
(
[StoreLevelID]
)
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Roles](
[StoreRole] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StoreRoleMappingID] [int] NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[StoreRole] ASC,
[StoreRoleMappingID] ASC
),
CONSTRAINT [FK_Roles_Mapping] FOREIGN KEY
(
[StoreRoleMappingID]
)
REFERENCES [dbo].[Mapping]
(
[StoreRoleMappingID]
)
) ON [PRIMARY]
END
INSERT INTO Definition (StoreLevelID, StoreLevelName)
SELECT '1','Current' UNION ALL
SELECT '2','All' UNION ALL
SELECT '3','Region' UNION ALL
SELECT '4','District' UNION ALL
SELECT '5','Store'
GO
INSERT INTO Mapping (StoreRoleMappingID, StoreLevelID, ParentKey, Value, CompanyID, StoreID)
SELECT '1', '3', NULL, 'Region One', NULL, NULL UNION ALL
SELECT '2', '3', NULL, 'Region Two', NULL, NULL UNION ALL
SELECT '3', '4', '1', 'District One', '991', '99999' UNION ALL
SELECT '4', '4', '1', 'District Two', '992', '99999' UNION ALL
SELECT '5', '4', '2', 'District Three', '882', '888888' UNION ALL
SELECT '6', '4', '2', 'District Four', '883', '888888' UNION ALL
SELECT '7', '5', '3', '111/12345', '111', '12345' UNION ALL
SELECT '8', '5', '3', '222/67890', '222', '67890' UNION ALL
SELECT '9', '5', '4', '333/12345', '333', '12345' UNION ALL
SELECT '10', '5', '5', '444/67890', '444', '67890' UNION ALL
SELECT '11', '5', '6', '555/67890', '555', '67890'
GO
INSERT INTO Roles (StoreRole,StoreRoleMappingID)
SELECT 'BUILTIN\User','1' UNION ALL
SELECT 'BUILTIN\Administrator','1' UNION ALL
SELECT 'BUILTIN\Administrator','3' UNION ALL
SELECT 'BUILTIN\Administrator','7' UNION ALL
SELECT 'BUILTIN\Administrator','10' UNION ALL
SELECT 'BUILTIN\Administrator','6'
GO
Output for this kind of mapping should be:
<Stores>
<Region Description="Region One" CompanyStoreID="1" Value="1">
<District Description="District One" CompanyID="991" StoreID="99999" CompanyStoreID="3" Value="3">
<Store Description="111/12345" CompanyID="111" StoreID="12345" CompanyStoreID="7" Value="7" />
<Store Description="222/67890" CompanyID="222" StoreID="67890" CompanyStoreID="8" Value="8" />
</District>
<District Description="District Two" CompanyID="992" StoreID="99999" CompanyStoreID="4" Value="4">
<Store Description="333/12345" CompanyID="333" StoreID="12345" CompanyStoreID="9" Value="9" />
</District>
</Region>
<District Description="District Four" CompanyID="883" StoreID="888888" CompanyStoreID="6" Value="6">
<Store Description="555/67890" CompanyID="555" StoreID="67890" CompanyStoreID="11" Value="11" />
</District>
<Store Description="444/67890" CompanyID="444" StoreID="67890" CompanyStoreID="10" Value="10" />
</Stores>
April 16, 2009 at 2:40 pm
Hi
James (4/16/2009)
Please see code below for the object create scripts and sample data, sorry bout this:
No problem 🙂
Thanks for the sample DDL and data!
There are some things I don't understand:
* Why is the "Region" only shown for the first region?
* Why are the "District Four" without a "Region"?
* Why is "District Three" not shown?
* Why is store "444/67890" not shown with a region and/or district?
Since now we have this:
SELECT
m1.Value '@Description',
m1.StoreRoleMappingId '@CompanyStoreID',
m1.StoreRoleMappingId '@Value',
(
SELECT
m2.Value '@Description',
m2.CompanyID '@CompanyID',
m2.StoreID '@StoreId',
m2.StoreRoleMappingId '@CompanyStoreID',
m2.StoreRoleMappingId '@Value',
(
SELECT
m3.Value '@Description',
m3.CompanyID '@CompanyID',
m3.StoreID '@StoreID',
m3.StoreRoleMappingID '@CompanyStoreID',
m3.StoreRoleMappingID '@Value'
FROM Mapping m3
WHERE m2.StoreRoleMappingID = m3.ParentKey
FOR XML PATH('Store'), TYPE
)
FROM Mapping m2
WHERE m1.StoreRoleMappingID = m2.ParentKey
FOR XML PATH('District'), TYPE
)
FROM Definition d
JOIN Mapping m1 ON d.StoreLevelID = m1.StoreLevelID
WHERE StoreLevelName = 'Region'
FOR XML PATH('Region'), ROOT('Stores')
Greets
Flo
April 16, 2009 at 3:38 pm
Florian Reischl (4/16/2009)
There are some things I don't understand:* Why is the "Region" only shown for the first region?
* Why are the "District Four" without a "Region"?
* Why is "District Three" not shown?
* Why is store "444/67890" not shown with a region and/or district?
* Why is the "Region" only shown for the first region?
- Because that is the only region being mapped from the Roles table to Mapping table.
* Why are the "District Four" without a "Region"?
- Because "District Four" doesn't belong to a certain Region that is mapped from the Roles table and "District Four" however is mapped directly from the Roles table.
* Why is "District Three" not shown?
- That is because the ParentKey of "District Three" is "Region Two" from Mapping table which is NOT included in the mapping specified on Roles table
* Why is store "444/67890" not shown with a region and/or district?
- Because "444/67890" store doesn't belong to any Region or District and is directly mapped from Roles table
April 16, 2009 at 4:01 pm
I'm not sure if it is possible to add different element names in same level. If it is I don't know. Sorry.
Best wishes
Flo
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply