March 9, 2010 at 7:49 am
Hello people!
I have a table like this:
[font="Courier New"]
ID | Name | Parent
---------------------------------
1 | IND | NULL
2 | INS | 5
3 | CON | NULL
4 | AUT | 1
5 | FIN | NULL
6 | PHA | 1
7 | CFIN | 5
8 | CMRKT | 7
[/font]
CREATE TABLE [dbo].[tblIndustryCodes](
[IdIndustry] [int] IDENTITY(1,1) NOT NULL,
[IndustryCode] [nvarchar](5) NULL,
[IndustryName] [nvarchar](50) NULL,
[ParentId] [int] NULL,
CONSTRAINT [PK_tblIndustryCodes] PRIMARY KEY CLUSTERED ( [IdIndustry] ASC))
INSERT INTO [tblIndustryCodes]
([IndustryCode]
,[IndustryName]
,[ParentId])
VALUES
('IND','Industry',NULL),
('PHARM','Pharmacy',1),
('FIN','Finance',NULL),
('CFIN','Corporate Finance',3),
('CMRKT','Capital Markets',4)
And I'd like to generate a xml file from it which is structured according to the parent IDs
like this (simplified)
<IND>
--<AUT>
--<PHA>
<CON>
<FIN>
--<CFIN>
----<CMRKT>
Its a SQL Server Express 2008
I tried FOR XM explicit (without success) The tree depth is theoretically unlimited, so there has to be some kind of recursion I guess.
I just played around with some SQL, but none of it worked at all. I never used FOR XML before and all the examples I found had a pre-defined max tree depth.
Any help is greatly appreciated!
March 9, 2010 at 8:32 am
Please post the DDL (CREATE TABLE statement) for the table, sample data (as a series of INSERT INTO statements), and the code you have written in attempting to solve your problem.
March 10, 2010 at 6:02 am
Here's what folks @ stackoverflow answered, which I found is (partly) working:
1. create a recursive function:
create function SelectChild(@key as int)
returns xml
begin
return (
select
[Key] as "@key",
[ParentKey] as "@parentkey",
dbo.SelectChild([Key])
from KEY_TABLE
where [ParentKey] = @key
for xml path('record'), type
)
end
then call the function in your select:
select
[Key] as "@key",
'' as "@parentkey",
dbo.SelectChild([Key])
from KEY_TABLE
where [ParentKey] is null
for xml path ('record')
March 10, 2010 at 8:27 am
The schema uses the design pattern named "Adjacency List" and needs to be converted with a recursive CTE to a "Path Enumeration" pattern before converting to XML.
With IndustryCodes_Path
([IndustryCode],[IdIndustry], LevelCnt, [IndustryName], PathEnum)
as
( SELECT [tblIndustryCodes].[IndustryCode]
, [tblIndustryCodes].[IdIndustry]
, 1 AS LevelCnt
, [tblIndustryCodes].[IndustryName]
, CAST([tblIndustryCodes].[IdIndustry] as varchar(4000) ) as PathEnum
FROM [tblIndustryCodes]
WHERE [ParentId] is null
UNION ALL
SELECT [tblIndustryCodes].[IndustryCode]
, [tblIndustryCodes].[IdIndustry]
, LevelCnt + 1 as LevelCnt
, [tblIndustryCodes].[IndustryName]
, CAST( PathEnum + '\' + cast([tblIndustryCodes].[IdIndustry] as varchar(255) ) as varchar(4000) )
as PathEnum
FROM IndustryCodes_Path
JOIN [tblIndustryCodes]
on [tblIndustryCodes].[ParentId] = IndustryCodes_Path.[IdIndustry]
)
select [IndustryCode]
from IndustryCodes_Path
order by PathEnum
FOR XML AUTO;
SQL = Scarcely Qualifies as a Language
March 10, 2010 at 10:24 am
thanks for your answer! But unfortunately this returns
<IndustryCodes_Path IndustryCode="IND" />
<IndustryCodes_Path IndustryCode="AUTO" />
<IndustryCodes_Path IndustryCode="PHARM" />
<IndustryCodes_Path IndustryCode="FIN" />
<IndustryCodes_Path IndustryCode="CFIN" />
<IndustryCodes_Path IndustryCode="CMRKT" />
<IndustryCodes_Path IndustryCode="CON" />
<IndustryCodes_Path IndustryCode="IMPL" />
<IndustryCodes_Path IndustryCode="STRAT" />
which is a flat hierarchy. I need the items like
<IndustryCodes_Path IndustryCode="IND">
<IndustryCodes_Path IndustryCode="AUTO" />
<IndustryCodes_Path IndustryCode="PHARM" />
</IndustryCodes_Path>
<IndustryCodes_Path IndustryCode="FIN">
<IndustryCodes_Path IndustryCode="CFIN">
<IndustryCodes_Path IndustryCode="CMRKT" />
</IndustryCodes_Path>
</IndustryCodes_Path>
<IndustryCodes_Path IndustryCode="CON">
<IndustryCodes_Path IndustryCode="IMPL" />
<IndustryCodes_Path IndustryCode="STRAT" />
</IndustryCodes_Path>
March 12, 2010 at 2:06 am
This thread is about a similar problem. Perhaps some of the suggested solutions there can be of any help...
http://qa.sqlservercentral.com/Forums/Topic879842-338-1.aspx
/Markus
March 12, 2010 at 4:09 am
thanks for the info!
I already answered the question myself based on a thread on stackoverflow.com
1. Create a recursive function
CREATE function SelectChild(@key as int)
returns xml
begin
return (
select
IdIndustry as "@key",
ParentId as "@parentkey",
IndustryCode as "@Code",
IndustryName as "@Name",
dbo.SelectChild(IdIndustry)
from tblIndustryCodes
where ParentId = @key
for xml path('record'), type
)
end
2. Build a SELECT statement, that calls the function
SELECT
IdIndustry AS "@key",
'' AS "@parentkey",
IndustryCode as "@Code",
IndustryName as "@Name",
dbo.SelectChild(IdIndustry)
FROM dbo.tblIndustryCodes
WHERE ParentId is null
FOR XML PATH ('record')
This creates a hierarchical XML, no matter how deep the tree actually is:
<record key="1" parentkey="" Code="IND" Name="Industry">
<record key="2" parentkey="1" Code="AUTO" Name="Automotive" />
<record key="3" parentkey="1" Code="PHARM" Name="Pharmaceuticals" />
</record>
<record key="4" parentkey="" Code="FIN" Name="Finance">
<record key="5" parentkey="4" Code="CFIN" Name="Corporate Finance">
<record key="6" parentkey="5" Code="CMRKT" Name="Capital Markets" />
</record>
</record>
<record key="7" parentkey="" Code="CON" Name="Cosulting">
<record key="8" parentkey="7" Code="IMPL" Name="Implementation" />
<record key="9" parentkey="7" Code="STRAT" Name="Strategy" />
</record>
March 30, 2011 at 3:05 pm
I tried your solution it works for multiple child leve hierarchy, however for siblings it doesnt seem to work
my desired output is as below:
<?xml version="1.0" encoding="utf-8"?>
<records>
<Parentrecord key="1" parentkey="" Code="IND" Name="Industry">
<Child key="2" parentkey="1" Code="AUTO" Name="Automotive" />
<Child key="3" parentkey="1" Code="PHARM" Name="Pharmaceuticals" />
</Parentrecord>
<Parentrecord key="4" parentkey="" Code="FIN" Name="Finance">
<child key="5" parentkey="4" Code="CFIN" Name="Corporate Finance">
<SubChild key="6" parentkey="5" Code="CMRKT" Name="Capital Markets" />
</child>
<child key="10" parentkey="4" Code="xyzn" Name="Corporate tele">
<SubChild key="12" parentkey="10" Code="xysb" Name="tele business" />
</child>
<child key="11" parentkey="4" Code="abcd" Name="Corporate IT">
<SubChild key="13" parentkey="11" Code="fghj" Name="IT hub" />
</child>
</Parentrecord>
<Parentrecord key="7" parentkey="" Code="CON" Name="Cosulting">
<child key="8" parentkey="7" Code="IMPL" Name="Implementation" />
<child key="9" parentkey="7" Code="STRAT" Name="Strategy" />
</Parentrecord>
</records>
Could you help please
March 31, 2011 at 1:47 am
I don't really understand, what you are trying to achieve. Could you elaborate?
March 31, 2011 at 6:57 am
Hi I require to take the table fields into a multilevel xml.
For example,
I have two tables
Table 1: Codes
Code ID | Code Value | Code Group | Code Desc
1 Manager HR manages work
2 Assistant HR
3 Employee1 Employee
4 Employee2 Employee
5 Sys Admin1 IT
6 Sys Admin2 IT
7 admin 1 Admin
8 admin 2 Admin
Table 2 : MappingTable
MapID | Code ID (FK) | ParentCodeID (FK)
1 1 NULL
2 2 NULL
3 3 1
4 4 1
5 5 1
6 6 1
7 7 3
8 8 4
So my XML should be looking like as below
<Codes>
<CodeGroup group="HR">
<Code CodeID="1" CodeValue="Manager" codeGroup="HR">
<ChildNodes group="Employee">
<Code codeID="3" CodeValue="employee1" codeGroup="Employee">
<ChildNodes group="Admin">
<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>
</ChildNodes>
</Code>
<Code codeID="4" CodeValue="employee2" codeGroup="Employee">
<ChildNodes group="Admin">
<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>
</ChildNodes>
</Code>
</ChildNodes>
<ChildNodes group="IT">
<Code codeID="5" CodeValue="sys Admin1" codeGroup="IT"/>
<Code codeID="6" CodeValue="sys Admin2" codeGroup="IT"/>
</ChildNodes>
</Code>
</CodeGroup>
</Codes>
With your above code I am able to get most of this structure, but I am not able to get the Employee and IT nodes grouped together under different ChildNodes of HR, they all come under single node directly under HR
I hope this is clear : This is what I am currently getting now
<Codes>
<CodeGroup group="HR">
<Code CodeID="1" CodeValue="Manager" codeGroup="HR">
<Code codeID="3" CodeValue="employee1" codeGroup="Employee">
<ChildNodes group="Admin">
<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>
</ChildNodes>
</Code>
<Code codeID="4" CodeValue="employee2" codeGroup="Employee">
<ChildNodes group="Admin">
<Code codeID="7" CodeValue="admin1" codeGroup="Admin"/>
</ChildNodes>
</Code>
<Code codeID="5" CodeValue="sys Admin1" codeGroup="IT"/>
<Code codeID="6" CodeValue="sys Admin2" codeGroup="IT"/> </Code>
</CodeGroup>
</Codes>
Please help
April 1, 2011 at 1:33 am
You should be able to accomplish this by adding an IF to the function and an additional column like "IsGroup" to your table.
HR and IT have IsGroup=true. (Or somethin like isPerson=False)
Within the function you check if the current row has the IsGroup Bit set or not and then create a group or standard entry.
Can't proveide code atm, maybe you try that out and see how far you make it ... I'm a bit in a hurry. Good luck!
April 8, 2011 at 3:03 pm
I tried this SP
SELECT codeMappingTable.codeID AS "@codeID",
codes.codeLiteral AS "@codeLiteral",
codes.codeGroup AS "@codeGroup"
,dbo.GetPartsSubTree(codeMappingTable.codeID)
FROM codeMappingTable,codes
WHERE codeMappingTable.ParentcodeID IS NULL AND codes.codeID = codeMappingTable.codeID
ORDER BY codes.codeGroup,codes.codeLiteral
FOR XML PATH('CodeParent'),ROOT('Codes'),TYPE
And Recursive function
ALTER FUNCTION [dbo].[GetPartsSubTree](@PartNumberID int)
RETURNS XML
BEGIN RETURN
(SELECT codeMappingTable.codeID AS "@codeID",
codes.codeLiteral AS "@codeLiteral",
codes.codeGroup AS "@codeGroup",
dbo.GetPartsSubTree(codeMappingTable.codeID)
FROM codeMappingTable,codes
WHERE codeMappingTable.ParentcodeID=@PartNumberID and codes.codeID = codeMappingTable.codeID
ORDER BY codes.codeGroup
FOR XML PATH('CodeParent'),ROOT('ChildGroup'),TYPE)
END
It gives me repeating nodes and If a parent has two types of children all come under common nodes under that parent the children are not categorised by thier type.. Please help
April 9, 2011 at 12:48 am
please provide some sample data as INSERTS, I will look over it when I'm in the office the next time (Wednesday I believe)
April 11, 2011 at 7:50 am
Please find the sample data
CREATE TABLE dept
(
codeID INT,
codeValue VARCHAR(50),
codeGroup VARCHAR(50)
)
INSERT dept
SELECT 1,'MANAGER','HR' UNION ALL
SELECT 3,'EMPLOYEE1','EMPLOYEE' UNION ALL
SELECT 7,'ADMIN1','ADMIN' UNION ALL
SELECT 4,'EMPLOYEE2','EMPLOYEE' UNION ALL
SELECT 5,'SYS ADMIN1','IT' UNION ALL
SELECT 6,'SYS ADMIN2','IT' UNION ALL
SELECT 2,'IDM1','INFRASTRUCTURE'
create TABLE debtMapping
(
mapID INT,
codeID INT,
parentCodeID INT
)
INSERT debtMapping
SELECT 1,1,NULL UNION ALL
SELECT 2,3,1 UNION ALL
SELECT 3,7,3 UNION ALL
SELECT 4,4,1 UNION ALL
SELECT 5,7,4 UNION ALL
SELECT 6,5,1 UNION ALL
SELECT 7,6,1 UNION ALL
SELECT 8,2,NULL
There is one more problem happening when I have 1000 records in the mapping table, the select statement in the stored procedure is probably duplicating the results so I get the same xml nodes duplicated thrice, I am not able to replicate it with small data. Please let me know if you need any more information.
April 11, 2011 at 8:24 am
I am able to rectify the duplicate record issue, now the only issue left is the xml format displayed, I want the main nodes to be grouped under <codeGroup node> and the Child nodes to be grouped under each <Child nodes group> the group is the factor to use to group elements.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply