Re: Storing hierarchies from XML into relational tables

You haven't made clear what sort of relational tables you plan to use to store the data from your hierarchy. Speculating something like:

create table #t1
(
ID INT IDENTITY PRIMARY KEY,
Name VARCHAR(MAX),
Parent VARCHAR(MAX)
)

Given an xml document that represents a heirarchy:

DECLARE @x xml
SET @x =
'<node name="name1" id="new">
<node name="name2" id="new"/>
<node name="name3" id="new">
<node name="name4" id="new"/>
<node name="name5" id="new"/>
</node>
</node>'

This would shred the new nodes from an xml document into the #t1 table:

INSERT INTO #t1
SELECT T.C.value('@name', 'VARCHAR(MAX)') as person,
T.C.value('../@name', 'VARCHAR(MAX)') as Parent
FROM @x.nodes('//node') T(C)
WHERE NOT EXISTS (SELECT * FROM #t1 WHERE name = T.C.value('@name', 'VARCHAR(MAX)'))

select * from #t1SELECT * FROM #t1

1 name1 NULL
2 name2 name1
3 name3 name1
4 name4 name3
5 name5 name3

Dan


"PMarino" <PMarino@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:E00BAEF2-19D9-4267-84D9-BEE1BD5F62FA@xxxxxxxxxxxxx:

Hi - I'm storing organization hierarchies in a SQL Server 2005 database in a
relational form. I can retrieve them easily, but now I need to save changes.
Given the sheer volume of data, I figured that repeated calls to the
database are Bad Things, so I thought of passing the changes to SQL as an XML
document. The problem is that the hierarchy is arbitrarily deep, and I can
have added, changed or deleted nodes. Does anyone have any examples of this?


The good news is that all of the elements in the hierarchy are of the same
type. One of the biggest issues that I see happening is if I add children to
a new node. How do I tell SQL the parent ID of these new nodes, since the
primary key isn't generated until they are inserted?

Example:

<node name="first level" id="new">
<node name="second level" id="new">
<node name="third level" id="new"/>
</node>
</node>

That's simply an example of course. I thought of passing a 'tempId' value
for new nodes, but how would I update them with the primary key so that child
nodes could use the real primary key?

I've also thought about making the document have only one level, and each
'new' node would have a temporary id. Child nodes of new nodes would
reference this temp Id, so that I didn't have to derive the parent from the
XML itself. This would allow me to make less passes, I think. Something
like this:

<node name="first level" tempId="1" parentId="0" />
<node name="second level" tempId="2" parentId="1" />
<node name="third level" tempId="3" parentId="2" />

So I would have to insert all of the new nodes and THEN assign the parents.
I'm ok with making multiple calls to the DB as long as it's a constant
number - like this:

Insert New Nodes
Delete selected Nodes
Update Nodes

Any help is appreciated - thanks!

.

Relevant Pages

  • Re: Storing hierarchies from XML into relational tables
    ... the parent id ... ID INT IDENTITY PRIMARY KEY, ... Given an xml document that represents a heirarchy: ...
    (microsoft.public.sqlserver.xml)
  • SOLVED (Was: Hierarch transversal problem with MySQL)
    ... But it does have a regex matching function, so I could match a parent to its ... Creating a Credit Card Validation Class With PHP ... Moving Beyond MySQL - High End Database Solutions ... hierarchy in a flat file. ...
    (comp.lang.php)
  • Re: Insert large amounts of data
    ... Most performance because you special case the processing to the XML format ... > Elements per Parent Element. ... There is also usually one Parent element ... > fastest with an estimated operator cost of 2.645. ...
    (microsoft.public.sqlserver.xml)
  • RE: XML documentation file name
    ... MSDN document on VB.NET Project Designer says "The Generate XML document ... XML documentation is automatically emitted into an XML ... Microsoft Online Community Support ...
    (microsoft.public.dotnet.languages.vb)
  • Re: [RFC][PATCH 1/2] memcg: res_counter hierarchy
    ... While several policy of hierarchy can be considered, ... create a child. ... prepare enough room in parent. ... One way to manage hierarchies other than via limits is to use shares (please see ...
    (Linux-Kernel)