Re: Storing hierarchies from XML into relational tables
- From: "Dan" <DanATPluralsight>
- Date: Sat, 15 Jul 2006 14:04:45 +0000
The table will need a primary key so that it can be updated.
create table t10
(
ID INT IDENTITY UNIQUE,
Name VARCHAR(100) PRIMARY KEY,
Parent INT,
CONSTRAINT K1 FOREIGN KEY (Parent) REFERENCES t10(ID)
)
This basic idea is the use the OUTPUT clause to get back the new ID's from the INSERT, then update the Parent field with the new ID's as appropriate. There are many variations on the, YMMV.
DECLARE @x xml
SET @x =
'<node name="name1" id="new">
<node name="name2" id="new">
<node name="name12" id="new"/>
<node name="name11" id="new"/>
</node>
<node name="name3" id="new">
<node name="name15" id="new"/>
</node>
</node>';
--This would shred the new nodes from an xml document into the #t1 table:
DECLARE @new TABLE(ID INT, name VARCHAR(MAX))
INSERT INTO T10
OUTPUT inserted.ID, inserted.Name INTO @new
SELECT T.C.value('@name', 'VARCHAR(MAX)') as person,
NULL as Parent FROM
@x.nodes('//node') T(C) WHERE NOT EXISTS
(SELECT * FROM T10 WHERE name = T.C.value('@name', 'VARCHAR(MAX)'));
WITH new
AS
(
SELECT T.C.value('@name', 'VARCHAR(MAX)') name, T.C.value('../@name', 'VARCHAR(MAX)') as pname
FROM @x.nodes('//node') AS T(C)
),
Parents
AS
(
SELECT N.name, T10.ID FROM new as N JOIN T10
ON N.pname = T10.name
)
UPDATE T10 SET Parent = Parents.ID FROM Parents WHERE Parents.Name = T10.name
SELECT * FROM T10
1 name1 NULL
5 name11 2
3 name12 2
6 name15 4
2 name2 1
4 name3 1
Dan
"PMarino" <PMarino@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:9B683F99-5C7D-4C33-86D5-14A0CCDC36B2@xxxxxxxxxxxxx:
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!
>
>
.
- References:
- Re: Storing hierarchies from XML into relational tables
- From: PMarino
- Re: Storing hierarchies from XML into relational tables
- Prev by Date: Re: Storing hierarchies from XML into relational tables
- Next by Date: Re: Connection String Problem
- Previous by thread: Re: Storing hierarchies from XML into relational tables
- Next by thread: Selecting Child Nodes
- Index(es):
Relevant Pages
|
