Insert with parent/child relation

[Andreas Leitner]

Hi,

I have a problem merging XML data back into the DB:

say I have the following two tables:

Parent:
ParentId (PK, AutoInc)
Name (vchar)

Child:
ChildId (PK, AutoInc)
ParentId (FK, not null)
Name (vchar)

I have a XML schema that maps these two tables to an xml structure of
the form:
---
<Parent ParentId="1">
<Name>A</Name>
<Child ChildId="1" ParentId="1">
<Name>A.1</Name>
</Child>
<Child ChildId="2" ParentId="1">
<Name>A.2</Name>
</Child>
</Parent>

<Parent ParentId="2">
<Name>B</Name>
<Child ChildId="3" ParentId="2">
<Name>B.1</Name>
</Child>
<Child ChildId="4" ParentId="2">
<Name>B.2</Name>
</Child>
</Parent>
---

Now what I want to do is let the user create such an XML file and the
import that into the DB (which results in adding new children and
parents). The user doesn't and cannot know anything about 'id's. So he
just ommits them. He will give my application the following data:

--
<Parent>
<Name>C</Name>
<Child>
<Name>C.1</Name>
</Child>
<Child>
<Name>C.2</Name>
</Child>
</Parent>
---

The user ommits all the 'id's. Of course, how could he know what 'id's
can be used? Also the user potentially didn't write the xml file from
scratch, he could have used a previously exported file as basis. In this
case the file would look like this:

---
<Parent ParentId="1">
<Name>A</Name>
<Child ChildId="1" ParentId="1">
<Name>A.1</Name>
</Child>
<Child ChildId="2" ParentId="1">
<Name>A.2</Name>
</Child>
</Parent>
<Parent ParentId="2">
<Name>B</Name>
<Child ChildId="3" ParentId="2">
<Name>B.1</Name>
</Child>
<Child ChildId="4" ParentId="2">
<Name>B.2</Name>
</Child>
</Parent>
<Parent>
<Name>C</Name>
<Child>
<Name>C.1</Name>
</Child>
<Child>
<Name>C.2</Name>
</Child>
</Parent>
---

Now I have tried to get this working for several days now. Using all
different kinds of approaches, but nothing seems to work reliably. How
does one solve this problem?

many thanks in advance,
Andreas

 

[Andreas Leitner]

Hi,
Check this links, should help you out

http://msdn.microsoft.com/library/en-us/dnbda/html/bdadotnetdata4.asp?frame=
true
http://msdn.microsoft.com/msdnmag/issues/03/07/DataPoints/toc.asp?frame=true

Hi Manjo,

thanks a lot for your answer. FWIW: meanwhile I have found another
interesting link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;320301#7


Andreas