The database I must insert my data into is rather old, and poorly designed.
We have a situation were a table can be the child of many tables; the "key"
in the child can reference more than one parent. I have created a simple
example that gets at the heart of my problem; how can I copy an identity
column from the parent into more than one column in the child.
The sample below produces:
Parent
--
1 TestData
Child
--
1 1 0 DataTest -> I need 1 1 1 DataTest
Using SQL 2000, SQLXML 3.0 SP3. Table, Schema and XML:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
GO
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
GO
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<!-- This double copy does not work -->
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID"
child="Child"
child-key="ParentID ParentID_B" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Parent" sql:relation="Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ParentID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Foo" type="xsd:string" />
<xsd:element name="Child" sql:relation="Child"
sql:relationship="Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChildID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Bar" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
Thanks in advance.I also discovered that if I extend my sample data another row; 1 more parent
and child, that the subsequent values of ParentID_B increase based on the
number of parent's. Maybe I do not understand some mechanics going on behind
the scenes. Clearly there is a pattern here.
The XML below produces
ParentID Foo
-- --
1 TestData
2 TestData
ChildID ParentID ParentID_B Bar
-- -- -- --
1 1 0 DataTest
2 2 1 DataTest
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>|||What is the exactly question here? It looks like your previous post is
deleted.
What is the column type for ParentID_B?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>I also discovered that if I extend my sample data another row; 1 more
>parent
> and child, that the subsequent values of ParentID_B increase based on the
> number of parent's. Maybe I do not understand some mechanics going on
> behind
> the scenes. Clearly there is a pattern here.
> The XML below produces
> ParentID Foo
> -- --
> 1 TestData
> 2 TestData
> ChildID ParentID ParentID_B Bar
> -- -- -- --
> 1 1 0 DataTest
> 2 2 1 DataTest
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> </ROOT>|||My original post described my problem, but the second post was to describe
some new information I had learned after playing with the code for a day. Th
e
original post is still valid, it contains a table schema, xsd and xml
document.
However, I will try and re-describe this:
Imagine two tables, one called "Parent" and one called "Child". The schema
for these tables is as follows:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
In "Child" the value of "ParentID" and "ParentID_B" should be the same; they
need to be populated with the value from Parent.ParentID after the insert.
(See the xsd in the original post)
I thought I could use a relationship like this to populate both columns when
using SQLXML:
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID" <-This is copied more than once (does not
work)
child="Child"
child-key="ParentID ParentID_B" />
However, what I find is that the value of ParentID_B will be zero in the
first child, 1 in the second, 2 in the third etc. In other words, ParentID
will get the correct value, but ParentID_B will always be one less than
ParentID.
Since my relationship above does not work, how can I copy the values for
subsequent children of “Parent” and have both ParentID and ParentID_B be
the
same value from the table "Parent"?
This example is heavily oversimplified, my real database is much more
complex, but this shows the mechanics of my problem more clearly I believe.
"Bertan ARI [MSFT]" wrote:
> What is the exactly question here? It looks like your previous post is
> deleted.
> What is the column type for ParentID_B?
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>
>|||This looks like a bug in our code. Thanks for reporting it
Unfortunately, I don't know any simple workaround for now. Is it possible
for you to create a script that will update ParentID_B columns after
bulkload?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FEEBE11C-8B69-43CF-9635-B473A36CE22A@.microsoft.com...
> My original post described my problem, but the second post was to describe
> some new information I had learned after playing with the code for a day.
> The
> original post is still valid, it contains a table schema, xsd and xml
> document.
> However, I will try and re-describe this:
> Imagine two tables, one called "Parent" and one called "Child". The schema
> for these tables is as follows:
> CREATE TABLE Parent (
> ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> Foo VarChar(10) NULL
> )
> CREATE TABLE Child (
> ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> ParentID Int NOT NULL,
> ParentID_B Int NOT NULL,
> Bar VarChar(10) NULL
> )
> In "Child" the value of "ParentID" and "ParentID_B" should be the same;
> they
> need to be populated with the value from Parent.ParentID after the insert.
> (See the xsd in the original post)
> I thought I could use a relationship like this to populate both columns
> when
> using SQLXML:
> <sql:relationship name="Children"
> parent="Parent"
> parent-key="ParentID ParentID" <-This is copied more than once (does not
> work)
> child="Child"
> child-key="ParentID ParentID_B" />
> However, what I find is that the value of ParentID_B will be zero in the
> first child, 1 in the second, 2 in the third etc. In other words, ParentID
> will get the correct value, but ParentID_B will always be one less than
> ParentID.
> Since my relationship above does not work, how can I copy the values for
> subsequent children of "Parent" and have both ParentID and ParentID_B be
> the
> same value from the table "Parent"?
> This example is heavily oversimplified, my real database is much more
> complex, but this shows the mechanics of my problem more clearly I
> believe.
>
> "Bertan ARI [MSFT]" wrote:
>
