April 6, 2009 at 4:26 pm
I am having problem on how to generate an XML out of this data below.
Sample Data:
SELECT 'Region1' AS Description,'NULL' AS StoreID,'NULL' AS CompanyID,0 AS Level UNION ALL
SELECT 'District1','NULL','NULL','1' UNION ALL
SELECT '111/12345','111','12345','2' UNION ALL
SELECT '222/12345','222','12345','2' UNION ALL
SELECT 'District2','NULL','NULL','1' UNION ALL
SELECT '333/3456','333','3456','2'
The expected XML output should be in this format:
<Region Description="Region1">
<District Description="District1">
<Store CompanyID="12345" StoreID="111" Description="111/12345" />
<Store CompanyID="12345" StoreID="222" Description="222/12345" />
</District>
<District Description="District2">
<Store CompanyID="3456" StoreID="333" Description="333/3456" />
</District>
</Region>
I tried using the FOR XML PATH() but I failed. Any sample working code is highly appreciated.
Thanks for your time.
April 6, 2009 at 5:32 pm
SELECT 'Region1' AS Description,'NULL' AS StoreID,'NULL' AS CompanyID,0 AS Level UNION ALLSELECT 'District1','NULL','NULL','1' UNION ALL
SELECT '111/12345','111','12345','2' UNION ALL
SELECT '222/12345','222','12345','2' UNION ALL
SELECT 'District2','NULL','NULL','1' UNION ALL
SELECT '333/3456','333','3456','2'
There's no way of determining which rows where Level=2 should be the child elements of which rows where Level= 1 in your data. I realise that you have attempted to specify this information by the order of the rows, but this row order is not significant to SQL Server. Also, you are effectively modelling 3 separate entities - Region, District and Store - in one table, which doesn't make much sense. Is this data derived from other tables that correctly model your data entities and the relations between them? If so it would be better to generate the XML from those tables instead.
April 6, 2009 at 6:38 pm
I've changed your table to include ID and ParentID columns, which allow the parent-child relationship of the rows to be properly defined. Note that this amended table still has the ugly feature that it is modelling 3 separate entities in one table, but I'll ignore that issue for the purposes of the example query below.
Here's the amended test data:
CREATE TABLE #xmlData (
ID int NOT NULL PRIMARY KEY,
ParentID int,
Description varchar(50),
StoreID varchar(10),
CompanyID varchar(10)
)
INSERT INTO #xmlData (ID, ParentID, Description, StoreID, CompanyID)
SELECT 1, NULL, 'Region1', NULL, NULL UNION ALL
SELECT 2, 1, 'District1', NULL, NULL UNION ALL
SELECT 3, 2, '111/12345', '111', '12345' UNION ALL
SELECT 4, 2, '222/12345', '222', '12345' UNION ALL
SELECT 5, 1, 'District2', NULL, NULL UNION ALL
SELECT 6, 5, '333/3456', '333', '3456'
Given the above data, you can generate the XML using the FOR XML PATH clause as follows:
SELECT
X0.Description AS [@Description],
(
SELECT
X1.Description AS [@Description],
(
SELECT
X2.CompanyID AS [@CompanyID],
X2.StoreID AS [@StoreID],
X2.Description AS [@Description]
FROM #xmlData X2
WHERE (X2.ParentID = X1.ID)
FOR XML PATH('Store'), TYPE
)
FROM #xmlData X1
WHERE (X1.ParentID = X0.ID)
FOR XML PATH('District'), TYPE
)
FROM #xmlData X0
WHERE (X0.ParentID IS NULL)
FOR XML PATH('Region')
This query generates the following XML:
<Region Description="Region1">
<District Description="District1">
<Store CompanyID="12345" StoreID="111" Description="111/12345" />
<Store CompanyID="12345" StoreID="222" Description="222/12345" />
</District>
<District Description="District2">
<Store CompanyID="3456" StoreID="333" Description="333/3456" />
</District>
</Region>
April 7, 2009 at 6:20 am
Thanks for your prompt response andrewd.smith.
Actually I have those data that you need by the time I generate the hierarchical data from the mapping table. I apologize for not making things clear. I have here the sample tables in the attachment. I was trying to query on the StoreRoleMapping table in hierarchical way.
The order of hierarchy for levels should be Region -> District -> Stores. And so if I want to display all stores under Region1 (assuming the StoreRoleMappingID on StoreRolesTable is 3) then it should include the District 1 & 2 under StoreRoleMappingID 5,6 and retrieve the stores under those districts which are under StoreRoleMappingID 9,10, and 11.
If I wanted to display all stores under District1 only using StoreRoleMappingID 5, then it should display stores under StoreRoleMappingID 9 and 10.
All display should be in XML format using the xml hierarchy that I showed from the previous post. Any insights on how to deal with this kind of scenario is greatly appreciated.
Thanks for the help.
April 7, 2009 at 6:57 am
James,
Glad to be of assistance.
Thanks for your feedback.
--Andrew
April 7, 2009 at 7:03 am
andrewd.smith (4/7/2009)
James,Glad to be of assistance.
Thanks for your feedback.
--Andrew
Actually I have those data that you need by the time I generate the hierarchical data from the mapping table. I apologize for not making things clear. I have here the sample tables in the attachment. I was trying to query on the StoreRoleMapping table in hierarchical way.
The order of hierarchy for levels should be Region -> District -> Stores. And so if I want to display all stores under Region1 (assuming the StoreRoleMappingID on StoreRolesTable is 3) then it should include the District 1 & 2 under StoreRoleMappingID 5,6 and retrieve the stores under those districts which are under StoreRoleMappingID 9,10, and 11.
If I wanted to display all stores under District1 only using StoreRoleMappingID 5, then it should display stores under StoreRoleMappingID 9 and 10.
All display should be in XML format using the xml hierarchy that I showed from the previous post. Any insights on how to deal with this kind of scenario is greatly appreciated.
Thanks for the help.
April 7, 2009 at 8:06 am
If I wanted to display all stores under District1 only using StoreRoleMappingID 5, then it should display stores under StoreRoleMappingID 9 and 10.
What XML do you want to be generated in this case:
<Region Description="Region1">
<District Description="District1">
<Store CompanyID="12345" StoreID="111" Description="111/12345" />
<Store CompanyID="12345" StoreID="222" Description="222/12345" />
</District>
</Region>
or
<District Description="District1">
<Store CompanyID="12345" StoreID="111" Description="111/12345" />
<Store CompanyID="12345" StoreID="222" Description="222/12345" />
</District>
April 7, 2009 at 9:18 am
andrewd.smith (4/7/2009)
If I wanted to display all stores under District1 only using StoreRoleMappingID 5, then it should display stores under StoreRoleMappingID 9 and 10.
What XML do you want to be generated in this case:
<Region Description="Region1"><District Description="District1">
<Store CompanyID="12345" StoreID="111" Description="111/12345" />
<Store CompanyID="12345" StoreID="222" Description="222/12345" />
</District>
</Region>
or
<District Description="District1"><Store CompanyID="12345" StoreID="111" Description="111/12345" />
<Store CompanyID="12345" StoreID="222" Description="222/12345" />
</District>
It should display the second one. But if you would be so kind also on how to display the first one then that would be great. I really appreciate your help.
Thank you for your time.
April 7, 2009 at 10:38 am
The T-SQL below queries your StoreRoleMapping table to generate the XML. You can specify the StoreRoleMappingID (?primary) column value of any row where the StoreRoleLevelID column value is 3 (Region), 4 (District) or 5 (Store).
DECLARE @StoreRoleMappingID int
DECLARE @StoreLevelID int
SELECT @StoreRoleMappingID = 3
SELECT @StoreLevelID = StoreRoleLevelID FROM dbo.StoreRoleMapping
WHERE (StoreRoleMappingID = @StoreRoleMappingID)
IF (@StoreLevelID = 3) BEGIN /* Region */
SELECT
X0.Value AS [@Description],
(
SELECT
X1.Value AS [@Description],
(
SELECT
X2.CompanyID AS [@CompanyID],
X2.StoreID AS [@StoreID],
X2.Value AS [@Description]
FROM dbo.StoreRoleMapping X2
WHERE (X2.Parent = X1.StoreRoleMappingID)
AND (X2.StoreRoleLevelID = 5)
FOR XML PATH('Store'), TYPE
)
FROM dbo.StoreRoleMapping X1
WHERE (X1.Parent = X0.StoreRoleMappingID)
AND (X1.StoreRoleLevelID = 4)
FOR XML PATH('District'), TYPE
)
FROM dbo.StoreRoleMapping X0
WHERE (X0.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('Region')
END
ELSE IF (@StoreLevelID = 4) BEGIN /* District */
SELECT
X1.Value AS [@Description],
(
SELECT
X2.CompanyID AS [@CompanyID],
X2.StoreID AS [@StoreID],
X2.Value AS [@Description]
FROM dbo.StoreRoleMapping X2
WHERE (X2.Parent = X1.StoreRoleMappingID)
AND (X2.StoreRoleLevelID = 5)
FOR XML PATH('Store'), TYPE
)
FROM dbo.StoreRoleMapping X1
WHERE (X1.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('District')
END
ELSE IF (@StoreLevelID = 5) BEGIN /* Store */
SELECT
X2.CompanyID AS [@CompanyID],
X2.StoreID AS [@StoreID],
X2.Value AS [@Description]
FROM dbo.StoreRoleMapping X2
WHERE (X2.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('Store')
END
If you wish to generate the 3-level XML hierarchy even if the @StoreRoleMappingID variable specifies a District or Store node, then the following should do the job.
EDIT: The query I posted originally was incorrect. Version below has been corrected.
DECLARE @StoreRoleMappingID int
DECLARE @StoreLevelID int
SELECT @StoreRoleMappingID = 5
SELECT @StoreLevelID = StoreRoleLevelID FROM dbo.StoreRoleMapping
WHERE (StoreRoleMappingID = @StoreRoleMappingID)
IF (@StoreLevelID = 3) BEGIN /* Region */
SELECT
X0.Value AS [@Description],
(
SELECT
X1.Value AS [@Description],
(
SELECT
X2.CompanyID AS [@CompanyID],
X2.StoreID AS [@StoreID],
X2.Value AS [@Description]
FROM dbo.StoreRoleMapping X2
WHERE (X2.Parent = X1.StoreRoleMappingID)
AND (X2.StoreRoleLevelID = 5)
FOR XML PATH('Store'), TYPE
)
FROM dbo.StoreRoleMapping X1
WHERE (X1.Parent = X0.StoreRoleMappingID)
AND (X1.StoreRoleLevelID = 4)
FOR XML PATH('District'), TYPE
)
FROM dbo.StoreRoleMapping X0
WHERE (X0.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('Region')
END
ELSE IF (@StoreLevelID = 4) BEGIN /* District */
SELECT
X0.Value AS [@Description],
(
SELECT
X1.Value AS [@Description],
(
SELECT
X2.CompanyID AS [@CompanyID],
X2.StoreID AS [@StoreID],
X2.Value AS [@Description]
FROM dbo.StoreRoleMapping X2
WHERE (X2.Parent = X1.StoreRoleMappingID)
AND (X2.StoreRoleLevelID = 5)
FOR XML PATH('Store'), TYPE
)
FROM dbo.StoreRoleMapping X1
WHERE (X1.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('District'), TYPE
)
FROM dbo.StoreRoleMapping X0
INNER JOIN dbo.StoreRoleMapping X1J
ON (X1J.Parent = X0.StoreRoleMappingID AND X1J.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('Region')
END
ELSE IF (@StoreLevelID = 5) BEGIN /* Store */
SELECT
X0.Value AS [@Description],
(
SELECT
X1.Value AS [@Description],
(
SELECT
X2.CompanyID AS [@CompanyID],
X2.StoreID AS [@StoreID],
X2.Value AS [@Description]
FROM dbo.StoreRoleMapping X2
WHERE (X2.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('Store'), TYPE
)
FROM dbo.StoreRoleMapping X1
INNER JOIN dbo.StoreRoleMapping X2J
ON (X2J.Parent = X1.StoreRoleMappingID AND X2J.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('District'), TYPE
)
FROM dbo.StoreRoleMapping X0
INNER JOIN dbo.StoreRoleMapping X1J
ON (X1J.Parent = X0.StoreRoleMappingID)
INNER JOIN dbo.StoreRoleMapping X2J
ON (X2J.Parent = X1J.StoreRoleMappingID AND X2J.StoreRoleMappingID = @StoreRoleMappingID)
FOR XML PATH('Region')
END
April 7, 2009 at 4:27 pm
Andrew,
Yes it is okay now. Thanks for the great script, it is working...I really appreciate it.
One thing I have been working on though (which was an added functionality lately) is that if a user has multiple-roles, like he/she can be an Administrator or User, it should retrieve all stores under each role mapping. Also in addition, stores that is present under a certain Branch/District should not be displayed outside if there are roles that are mapped to a store directly, if not then it will include in the displayed stores.
Thanks,
- James
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply