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

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

I have a Web Service one of whose methods returns a list of items, each of which possesses another list of items:

<TopLevelItems>
    <TopLevelItem field1="a" field2="b" ...>
        <LowLevelItem fieldA="1" fieldB="2" .../>
        <LowLevelItem fieldA="3" fieldB="4" .../>
    </TopLevelItem>
</TopLevelItems>

These lists are retrieved from a SQL Server database using simple queries (both TopLevelItem and LowLevelItem correspond to related tables in the database).

Until now, to retrieve all this data, I needed two queries: one to retrieve top level items, which was executed once; and another to retrieve low level items, which was executed once per top level item.

However, this seems to be 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 it possible? If so, how?

Answers

Hierarchical data in SQL server can be obtained using FOR XML. In this case, you would just need to write a query to join tables, then parent-child relationships will manifest as nested XML elements:

DECLARE @sites TABLE ( ID INT, Name VARCHAR(50) )
INSERT  INTO @sites
VALUES  ( 1,  abc  ),
        ( 2,  def  )

DECLARE @siteEnergy TABLE
  (
    SiteFK INT,
    Month INT,
    Energy INT
  )
INSERT  INTO @siteEnergy
VALUES  ( 1, 1, 50 ),
        ( 1, 2, 49 ),
        ( 1, 3, 50 ),
        ( 2, 1, 33 ),
        ( 2, 2, 34 ),
        ( 2, 3, 50 )

SELECT  *
FROM    @sites site
        JOIN @siteEnergy siteEnergy ON site.id = siteEnergy.sitefk
FOR     XML AUTO, ROOT( SiteInformation )

Results:

<SiteInformation>
  <site ID="1" Name="abc">
    <siteEnergy SiteFK="1" Month="1" Energy="50" />
    <siteEnergy SiteFK="1" Month="2" Energy="49" />
    <siteEnergy SiteFK="1" Month="3" Energy="50" />
  </site>
  <site ID="2" Name="def">
    <siteEnergy SiteFK="2" Month="1" Energy="33" />
    <siteEnergy SiteFK="2" Month="2" Energy="34" />
    <siteEnergy SiteFK="2" Month="3" Energy="50" />
  </site>
</SiteInformation>

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/5328725/may-i-retrieve-a-hierarchical-data-structure-from-a-sql-server-stored-procedure

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils