Stack Overflow
Stack Overflow

Questions tagged [hierarchyid]

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

152 questions
0
votes
1 answer

EntityFrameworkCore.SqlServer.HierarchyId

In my .NetCore3 project, I use HierarchyId to implement data tree structure. I am successful in storing as a tree, But I have trouble getting children. MyModel : public int Id { get; set; } [Required] [StringLength(256)] public…
Ehsan ebrahimi
  • 51
  • 5
0
votes
2 answers

How to convert HierarchyId to Child/Parent (Tree) structure in C# dotnet?

For some reason such as performance, I have to use HiarachyId in my database. I have to convert the HierarchyId data type to JSON to show up in FancyTree. I Use the solution here but won't work. My code was static void Main(string[] args) { …
Mohammad
  • 1,197
  • 2
  • 13
  • 30
0
votes
0 answers

How to move hierarchyid subtree if the new parent has already got children?

I guess the problem is described in the title pretty well. I've got a table like this: CREATE TABLE [Employees] ( [Id] INT identity(1, 1) PRIMARY KEY ,[Hid] HIERARCHYID NOT NULL ,[Name] VARCHAR(50) NULL ,[Secondname] VARCHAR(50) ,[Surname]…
Victorex
  • 1
  • 1
0
votes
2 answers

Get value from c# anonymous Type using HierarchyId

I am trying to use SqlHierarchyId in .net In my controller I am using anonymous type to get value from database. [HttpGet("{id}")] public async Task> GetDomain(int id) { object domain = from x in _context.Domains.Where(x =>…
Ektiva
  • 3
  • 1
0
votes
0 answers

How to get script for hierarchyid datatype column

Below table contains company_id, parent company_id, company name three columns is there I want to create one column with hierarchyid datatype of column name as node. company_id parentcompany_id companyname 826FDFF6-E1FC-44BA-94A1-000ACEAAA8D0 …
Vamsi Mukkanti
  • 1
  • 2
0
votes
1 answer

Convert HierarchyId to Hex string in C#, get raw ("representation") value for CSV export

So... for a CSV export from a SQL Server database, using C# and Entity Framework, I need to get the raw value of HierarchyId. The raw value looks like that in SSMS: 0x29F9DC and the output in my CSV is like that: /-25/-5/. The CSV is generated using…
Uranne
  • 25
  • 6
0
votes
2 answers

Migrate data type hierarchyid from SQL Server to Oracle VARCHAR2

I am migrating data from SQL Server to Oracle in a specified format. I have a table in SQL Server that has a column of type hierarchyid. I want to convert it into string, without changing its look. For example, if the hierarchyid type contain 0x78,…
user7727236
0
votes
1 answer

How to convert file patch to hierarchyid

I want to store a tree of all files and directories in the database. For my needs, the most appropriate structure for folders is: CREATE TABLE [Dirs] ([HID] hierarchyid NOT NULL, [FullPath] nvarchar(3800) NOT NULL); what is the best way to…
Vexator
  • 181
  • 3
  • 9
0
votes
0 answers

Recursive summation using hierarchyid with conditions

Referring to an earlier problem statement at: SQL Server Hierarchical Sum of column Code below for the implementation: USE tempdb; IF OBJECT_ID('dbo.Hierarchy') IS NOT NULL DROP TABLE dbo.[Hierarchy]; CREATE TABLE dbo.Hierarchy ( ID INT…
Palthis
  • 1
  • 1
0
votes
0 answers

How to cleanse data in multiple tables in a database?

I would like some ideas on the following problem. The System In a customer-centric system, the Customer_id is a right-aligned 9-digit code with 7 leading spaces. A valid entry would be ' 000000399'. There is a master CUSTOMERS table and nearly…
Konstantinos Katsoridis
  • 1
  • 2
0
votes
1 answer

Search hierarchyid by "right-most" node

I'm somewhat new to the hierarchyid datatype. I'm trying to represent entities in a given hierarchy. For the sake of argument, let's say they're people in the classic "boss of" hierarchy, but it could be anything. Originally, I came up with a…
Xedni
  • 3,662
  • 2
  • 16
  • 27
0
votes
1 answer

SQL join/merge two hierarchyid tables

Is there any way to join/merge two hierarchical tables in SQL Server? I have two sample tables: BOM and ComponentDetail declare @BOM table ( BomNode hierarchyid primary key, ComponentID int ) insert into @BOM values ('/',…
exegee
  • 1
  • 4
0
votes
2 answers

Convert hierarchy defined by position to SQL hierarchy id defined

I have a lot of data from an old system which defines the data in a Bill of Materials by the position it exists in a table. The BoM data table coming from the old system looks like ID level ItemNumber 1 1 TopItem 2 .2 …
Al-Biruni
  • 3
  • 4
0
votes
2 answers

Represent tree structure in Sql Server including Virtual paths

Hi I want to create a tree structure in a sql database. I need to have the following properties. Fast Select Easy to Select with only a string like Cat2/Cat4 -> Returns Cat5 & Cat6 Support of Virtual Categories Insert should be easy, but not…
gulbaek
  • 2,481
  • 13
  • 44
  • 65
0
votes
1 answer

SQL HierarchyId Sometimes Producing Wrong Path

I have a list of products, sorted using a HierarchyId, but for some of the items the full path up the hierarchy is producing the wrong result. Only for a few... Ill demonstrate below: SELECT ProductId ,Name ,FullName ,Hierarchy …
MysticVagabond
  • 259
  • 1
  • 13
1 2 3
…
10 11