1

I have a table in which columns1 is of type hierarchyID and when you do

Select Convert(varchar,Column1), Name from Table1

The result is

/1/         Root
/1/1/       Child 1
/1/2/       Child 2
/1/3/       Child 3
/1/2/1/     Child 2 Child 1
/1/2/2/     Child 2 Child 2
/1/2/3/     Child 2 Child 3
/1/2/2/1/   Child 2 Child 2 Child 1
/1/2/2/2/   Child 2 Child 2 Child 2

I want to convert this to json which will give me something like 

{  
   "Root":{  
      "Child 1":{},
      "Child 2":{  
         "Child 2 Child 1":{},
         "Child 2 Child 2":{  
            "Child 2 Child 2 Child 1":{},
            "Child 2 Child 2 Child 2":{}
         },
         "Child 2 Child 3":{}
       },
      "Child 3":{}
   }
}

Is this possible to do it in SQL server itself or can I do it by loading it into a c# variable or in javascript ? Ultimately I want to show this in a webpage, I intend to only show Root node at first which can be expanded to see its children and its children also can be expanded and so on. Thanks.

user1558076
  • 45
  • 5
  • 1
    is this possibly a duplicate of http://stackoverflow.com/questions/6945216/converting-flattened-hierarchical-data-from-sql-server-into-a-structured-json-ob? – Tadhg McDonald-Jensen Mar 01 '16 at 20:47
  • 1
    Somewhat. I also found this link http://stackoverflow.com/questions/19929696/converting-flattened-hierarchical-data-into-a-tree-structured-json?lq=1 which looks like it does what I need. I will give this a shot. – user1558076 Mar 01 '16 at 21:05

0 Answers0