How to insert/edit hierarchyid field as text in SQL studio

Question

In SQL server 2008 I have a hierarchical field (hierarchyid). How do I change its value or insert new row when using SQL Server Management Studio "edit rows" command? Is there a textual representation that will be converted to an ID?

Yes, I know I could do it using a query, but I would like to manually enter it as TEXT using the studio editor.

Thanks!

Answer 1

You can convert a HIERARCHYID to a string using:

hierarchyField.ToString();

You'll get something like '/1/', '/1/1/', '/1/2/' and so forth.

And you can convert such a string representation back to a HIERARCHYID with

SET hierarchyField = hierarchyid::Parse(string)

or

CAST('/2/' AS hierarchyid)

More details on this can be found on Technet