You don't give a lot of detail on your current structure, so I'm assuming each node in the hierarchy is in a simple table with a single parent relationship like so and the path being a string of ID like
/<rootNodeID>/<GPNodeID>/<PNodeID>/<LeadNodeID> and that you intend to store the settings in a property bag arrangement rather than needing to update the DB structure when a new setting is added to the applcation logic, resulting insomething like this:
============= ============ Settings
NodeID (PK) <--.-- NodeID (FK) =========
ParentID (FK) ---' Name (FK) ---> Name (PK)
In this arrangement you can pull all settings that have a value anywhere up the tree from a given location (with values closer like so:
SELECT Name = s.Name
Value = (SELECT TOP 1 ns.value
FROM NodeSetting ns
JOIN Nodes n ON n.NodeID = ns.NodeID AND '<PathToTargetNode>' LIKE n.FullPath+'%'
WHERE ns.Name = s.Name
ORDER BY LEN(n.FullPath) DESC
FROM Settings s
The use of a sub-query like this my not be efficient enough if you have many settings but whould be fine otherwise. Of more concern is using
LIKE that way: it will not be able to properly use any index on Nodes.FullPath so an index scan (or worse, a table scan) could be performed for each call of the sub-query. Off the top of my head I can't think of a way to significantly optimise this as a single query without adding new structure to your storage of the relationships between nodes such as keeping map between ascendants and descendants like:
(making sure to store the node's relationship with itself, as AscNode='NodeID', DescNode='NodeID', Distance=0). Then you can do:
SELECT Name = s.Name
Value = (SELECT TOP 1 s2.value
FROM RelationshipDistance r
JOIN Settings s2 ON n.NodeID = s.NodeID AND s2.Name = s.Name
WHERE r.Descendant = '<IDOfNodeYouAreLookingFor>'
ORDER BY r.Distance DESC
FROM Setting s
The disadvantage here is needing to maintain the extra structure (through triggers or work in another layer of your code).
Of course this is assuming that you want to return the settings from a single statement. You could always define a procedure that walks up the path collecting the settings in a temporary table or table variable, adding new settings found at each step towards the root - this is likely to be more efficient than a single statement version that causes index/table scans.
EDIT: Updated first example to more normal form so it does not require an extranious
distinct. See revision history for the difference.