Adjacency list from SQL table to XML file RRS feed

  • Question


    I'm having trouble generating an XML file from a sql table.  Basically its an HR Dept. table that has departmentID and ParentDepartmentID.  I need to create a hierarchal xml file that has a parent node, and all its departments, and if any of the sub departments have any below them, stick those departments in accordingly.  I can do this two ways...either through tSQL or through .NET recursive methods.  I've tried the recursion but can't wrap my head around it enough to get it working.


    Here is the table schema:


    Code Block
    CREATE TABLE [dbo].[DepartmentTree](
     [DepartmentID] [varchar](13) NOT NULL,
     [ParentDepartmentID] [varchar](13) NOT NULL,
     [Description] [varchar](30) NOT NULL,
     [ManagerEmployeeID] [varchar](13) NOT NULL
    ) ON [PRIMARY]



    here is the xml markup I'm looking for (or facsimile thereof...doesnt really matter)


    Code Block

    <?xml version="1.0" encoding="UTF-8" ?>

    <Department ID="1" Name="IT" Manager="12345">

    <Department ID="2" Name="IT Info. Sys" Manager="23456" />

    <Department ID="3" Name="IT Technical" Manager="34567">

    <Department ID="4" Name="IT Technical West" Manager="45678" />

    <Department ID="5" Name="IT Technical East" Manager="56789" />




    Here is my test data:


    DepartmentID ParentDepartmentID Description ManagerEmployeeID
    1 1 IT 12345
    2 1 IT Info. Sys 23456
    3 1 IT Technical 34567
    4 3 IT Technical West 45678
    5 3 IT Technical East 56789



    Does anyone have a suggestion as to how to do this?

    Friday, November 16, 2007 7:49 PM

All replies

  • If you are in SQL Server 2005 go to SQL Server T-SQL forum and post you will get help with CTE ( common table expression ) You then put the CTE in a DataSet and use the READ/WRITE XML methods.  This is not what you expect but few people know adjacency list and how to write one.  If you are not in SQL Server 2005 I don't think you can do it.


    Monday, November 19, 2007 7:38 PM
  • Hi,

    You could always create the xml file manually from the dataset.  In this case I think it would be simpler. The algorithm could be:
    • loop on every datarow and fill a Dictionary<int, list<int>> with the key being the parent id or 0 for roots and the children in the list .
      The result would be { { 0, {1}}, {1, {2,3}}, {3, {4,5}} }
    • Call a recursive function that adds a node based on the current:
       CreateNode(0, m_doc.DocumentElement);

    XmlNode CreateNode(int id)
        XmlNode result;
        if (id == 0) {
             Debug.Assert(m_dict[id].Count == 1);
        else {
            //create node based on m_table.Rows[id]
        foreach (int id in m_dict[id]) {
            XmlNode child = CreateNode(id);
        return result;

    Hope this helps,
    Sunday, December 2, 2007 7:23 PM