Hierarchy XML Construction

  • 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.

  • Provide the following:

    1. object creation scripts

    2. sample data

  • 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

  • Please have a look to the link in my signature for explanation how to provide some sample DDL and data.

    Thanks

    Flo

  • 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>

  • 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

  • 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

  • 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