Re: Storing hierarchies from XML into relational tables

Hi Dan - thanks for your reply. In terms of relational tables, the parent id
is the node id of the parent node. That's actually the source of my problem:
If a node is new and does not yet have an id, then it's difficult to insert a
child node into the tables, since I don't have an id yet.

So the table looks something like this;

create table #t1
(
ID INT IDENTITY PRIMARY KEY,
Name VARCHAR(MAX),
Parent INT -- Self-referencing Foreign key
)

Thanks,
Phil

"Dan" wrote:

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
    ... You haven't made clear what sort of relational tables you plan to use to store the data from your hierarchy. ... Parent VARCHAR ... Given an xml document that represents a heirarchy: ...
    (microsoft.public.sqlserver.xml)
  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)
  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)
  • Re: Connecting to the Same Table Twice
    ... RelationshipID (primary key) ... RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... you might put spouse names into a separate table linked to this table). ... for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Connecting to the Same Table Twice
    ... > RelationshipID (primary key) ... > RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... > you might put spouse names into a separate table linked to this table). ... > for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
Loading