hierarchy - May I retrieve a hierarchical data structure from a SQL Server stored procedure? -

I have a web service whose list gives one of the items, each of which has the second list of items :

  & lt; TopLevelItems & gt; & Lt; TopLevelItem field 1 = "A" field 2 = "B" ... & gt; & Lt; LowLevelItem field A = "1" field B = "2" ... ... & gt; & Lt; LowLevelItem field A = "3" fieldB = "4" ... /> & Lt; / TopLevelItem & gt; & Lt; / TopLevelItems & gt;   
 A list of these lists using simple queries (both  TopLevelItem  and  LowLevelItem  in the database correspond to related tables) using a SQL Server database Is obtained from  
 So far, to recover all this data, I need two questions: to retrieve the top-level objects, which was executed once; And the second to retrieve the low level items, which was executed once according to the top-level items.  
 However, it is highly inefficient. I would like to define a single stored procedure that performs all the necessary queries and retrieves the result as a hierarchical data structure. Is this possible? If so, then how?   
 
 
 The hierarchical data in SQL Server can be used for XML. In this case, you will need to type a query to join tables, then the parent's relationships will appear as nested XML elements:  
  DECLARE @sites table (ID INT , Insert the values ​​of the name VARCHAR (50) @ site (1, 'ABC'), (2, 'DEF) DECLARE @ SITE Entertainment Table (SITEFIC INT, month INT, ENERGY INT) @ SOCIAL ENTERING VALUES (1, 1, 50), (1, 3, 50), (2, 1, 33), (2, 2, 34), (2, 3, 50) SELECT * FROM Go to site site @ site   
 Results for: .id = siteEnergy.sitefk XML Auto, Root ('SiteInformation') on the site.  
  site notice & Gt; & lt; Site id = "1" name = "ABC"> SiteEnergy SiteFK = "1" month = "1" energy = "50" /> & site; siteEnergy SiteFK = "1 "Month =" 2 "energy =" 49 "/>  & gt; site / gt; & lt; site Id = "2" name = "def" & gt; siteEnergy SiteFK = "2" month = "1" energy = "33" /> & Lt; SiteEnergy SiteFK = "2" month = "2" energy = "34" /> & Lt; SiteEnergy SiteFK = "2" month = "3" energy = "50" /> & Lt; / Site & gt; & Lt; / SiteInformation & gt;