Yes thats correct..apologies my bad...

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

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

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

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

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=""...