parent-child question

Hi Folks
This one is driving me a little 'bonky'.
I have a (part - PRT_PRT) parent-child (part meta - PRT_MET) relationship
defined in the DB:
CREATE TABLE [dbo].[PRT_MET] (
[MetID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
[IsActive] [decimal](1, 0) NOT NULL ,
[MetaName] [varchar] (50) NULL ,
[MetaDesc] [varchar] (750) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PRT_PRT] (
[PrtID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
[RefID] [decimal](10, 0) NULL ,
[IsActive] [decimal](1, 0) NOT NULL ,
[MetID] [decimal](10, 0) NOT NULL ,
[PartName] [varchar] (50) NOT NULL ,
[PartDesc] [varchar] (750) NULL ,
[VersionNo] [smallint] NOT NULL
) ON [PRIMARY]
Here's my schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="PRT_PRT2PRT_MET"
parent="PRT_PRT"
parent-key="MetID"
child="PRT_MET"
child-key="MetID"
inverse="true" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="PRT_PRT">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="PrtId" type="xsd:decimal" />
<xsd:element name="RefId" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="PartName" type="xsd:string" />
<xsd:element name="PartDesc" type="xsd:string" />
<xsd:element name="VersionNo" type="xsd:short" />
<xsd:element sql:relation="PRT_MET" sql:relationship="PRT_PRT2PRT_MET"
name="PartMeta">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetaName" type="xsd:string" />
<xsd:element name="MetaDesc" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Here's my XML test file:
<ROOT>
<PRT_PRT>
<PrtId></PrtId>
<RefID>0</RefID>
<IsActive>1</IsActive>
<MetID></MetID>
<PartName>A-B-C</PartName>
<PartDesc>(Eng Assy - 3.5L 4V)</PartDesc>
<VersionNo>0</VersionNo>
<PRT_MET>
<MetID></MetID>
<IsActive>1</IsActive>
<MetaName>PartB</MetaName>
<MetaDesc>B</MetaDesc>
</PRT_MET>
</PRT_PRT>
</ROOT>
My .vbs code:
dim today
today = right(year(today),4) & "_" & right(month(today)+ 100,2) & "_" &
right(day(today)+100,2) & "_" & Right(datepart("h",today)+100, 2) &
Right(datepart("n",today)+100, 2)& Right(datepart("s",today)+100, 2)
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString =
"Provider=sqloledb;server=Karatzas14\PS;database=T ICv1;User
Id=myid;Password=mypwd"
objBL.ErrorLogFile = "C:\psplm NEW\TIC\FileExists\XMLSQL\errors" & today &
".log"
objBL.KeepNulls=True
objBL.KeepIdentity=False
'objBL.IgnoreDuplicateKeys=True
objBL.Execute "PRT_PRT2PRT_METv2.xsd", "test.xml"
set objBL=Nothing
Problem is that I need to update the 'MetID' field in PRT_PRT from the
PRT_MET insert (which uses an identity). Right now it's failing because it
needs to have a nonnull value in MetID (for PRT_PRT).
Again, thanks
tried everything I could dream up...
Rob
Let me understand this correct..
PRT_MET is a child table which generates the MetID using a auto identity and
this has to be propagated to MetID field in PRT_PRT which is the parent?
If this is the case, bulkload does cannot do this.
it can be other way around, prt-prt to prt-met.
HTH,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
> Hi Folks
> This one is driving me a little 'bonky'.
> I have a (part - PRT_PRT) parent-child (part meta - PRT_MET) relationship
> defined in the DB:
> CREATE TABLE [dbo].[PRT_MET] (
> [MetID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
> [IsActive] [decimal](1, 0) NOT NULL ,
> [MetaName] [varchar] (50) NULL ,
> [MetaDesc] [varchar] (750) NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[PRT_PRT] (
> [PrtID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
> [RefID] [decimal](10, 0) NULL ,
> [IsActive] [decimal](1, 0) NOT NULL ,
> [MetID] [decimal](10, 0) NOT NULL ,
> [PartName] [varchar] (50) NOT NULL ,
> [PartDesc] [varchar] (750) NULL ,
> [VersionNo] [smallint] NOT NULL
> ) ON [PRIMARY]
> Here's my schema:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="PRT_PRT2PRT_MET"
> parent="PRT_PRT"
> parent-key="MetID"
> child="PRT_MET"
> child-key="MetID"
> inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="PRT_PRT">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="PrtId" type="xsd:decimal" />
> <xsd:element name="RefId" type="xsd:decimal" />
> <xsd:element name="IsActive" type="xsd:decimal" />
> <xsd:element name="MetID" type="xsd:decimal" />
> <xsd:element name="PartName" type="xsd:string" />
> <xsd:element name="PartDesc" type="xsd:string" />
> <xsd:element name="VersionNo" type="xsd:short" />
> <xsd:element sql:relation="PRT_MET" sql:relationship="PRT_PRT2PRT_MET"
> name="PartMeta">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MetID" type="xsd:decimal" />
> <xsd:element name="IsActive" type="xsd:decimal" />
> <xsd:element name="MetaName" type="xsd:string" />
> <xsd:element name="MetaDesc" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> Here's my XML test file:
> <ROOT>
> <PRT_PRT>
> <PrtId></PrtId>
> <RefID>0</RefID>
> <IsActive>1</IsActive>
> <MetID></MetID>
> <PartName>A-B-C</PartName>
> <PartDesc>(Eng Assy - 3.5L 4V)</PartDesc>
> <VersionNo>0</VersionNo>
> <PRT_MET>
> <MetID></MetID>
> <IsActive>1</IsActive>
> <MetaName>PartB</MetaName>
> <MetaDesc>B</MetaDesc>
> </PRT_MET>
> </PRT_PRT>
> </ROOT>
> My .vbs code:
> dim today
> today = right(year(today),4) & "_" & right(month(today)+ 100,2) & "_" &
> right(day(today)+100,2) & "_" & Right(datepart("h",today)+100, 2) &
> Right(datepart("n",today)+100, 2)& Right(datepart("s",today)+100, 2)
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> objBL.ConnectionString =
> "Provider=sqloledb;server=Karatzas14\PS;database=T ICv1;User
> Id=myid;Password=mypwd"
> objBL.ErrorLogFile = "C:\psplm NEW\TIC\FileExists\XMLSQL\errors" & today &
> ".log"
> objBL.KeepNulls=True
> objBL.KeepIdentity=False
> 'objBL.IgnoreDuplicateKeys=True
> objBL.Execute "PRT_PRT2PRT_METv2.xsd", "test.xml"
> set objBL=Nothing
> Problem is that I need to update the 'MetID' field in PRT_PRT from the
> PRT_MET insert (which uses an identity). Right now it's failing because it
> needs to have a nonnull value in MetID (for PRT_PRT).
> Again, thanks
> tried everything I could dream up...
> Rob
|||unfortunately, that is the case...
(except I need to go many more levels deep in other load scenarios)
Is there anyway to do a look-up?
Thanks Rob
"Chandra Kalyanaraman [MSFT]" wrote:

> Let me understand this correct..
> PRT_MET is a child table which generates the MetID using a auto identity and
> this has to be propagated to MetID field in PRT_PRT which is the parent?
> If this is the case, bulkload does cannot do this.
> it can be other way around, prt-prt to prt-met.
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
>
>
|||maybe another approach might offer a solution...
is there any reason why I can't load from the 'bottom-up'? (having the
schema define that PRT_MET is the parent and that PRT_PRT is the child? I
have control of the XML stream generation.)
for my load case here, there's always only a 1-to-1 relationship being
created.
(this would be like 1 order detail row to 1 order master)
Rob
"Chandra Kalyanaraman [MSFT]" wrote:

> Let me understand this correct..
> PRT_MET is a child table which generates the MetID using a auto identity and
> this has to be propagated to MetID field in PRT_PRT which is the parent?
> If this is the case, bulkload does cannot do this.
> it can be other way around, prt-prt to prt-met.
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
>
>
|||hmmm...
this approach, actually does work (except for MetID not getting updated in
PRT_PRT).
Here's the changes:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="PRT_MET2PRT_PRT"
parent="PRT_MET"
parent-key="MetID"
child="PRT_PRT"
child-key="MetID"
inverse="true" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="PRT_MET">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetaName" type="xsd:string" />
<xsd:element name="MetaDesc" type="xsd:string" />
<xsd:element sql:relation="PRT_PRT" sql:relationship="PRT_MET2PRT_PRT"
name="PRT_PRT">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="PrtID" type="xsd:decimal" />
<xsd:element name="RefID" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="PartName" type="xsd:string" />
<xsd:element name="PartDesc" type="xsd:string" />
<xsd:element name="VersionNo" type="xsd:short" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
XML:
<ROOT>
<PRT_MET>
<MetID></MetID>
<IsActive>1</IsActive>
<MetaName>PartBody</MetaName>
<MetaDesc>228</MetaDesc>
<PRT_PRT>
<PrtId></PrtId>
<RefID>0</RefID>
<IsActive>1</IsActive>
<MetID></MetID>
<PartName>7G-228-AA</PartName>
<PartDesc>(Eng Assy - 3.5L 4V)</PartDesc>
<VersionNo>0</VersionNo>
</PRT_PRT>
</PRT_MET>
</ROOT>
anyway known way to get MetID updated (in PRT_PRT). any such thing as a
lookup, variable in .xsd (similar to XSL), etc.?
keeping fingers crossed...
Rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> maybe another approach might offer a solution...
> is there any reason why I can't load from the 'bottom-up'? (having the
> schema define that PRT_MET is the parent and that PRT_PRT is the child? I
> have control of the XML stream generation.)
> for my load case here, there's always only a 1-to-1 relationship being
> created.
> (this would be like 1 order detail row to 1 order master)
> Rob
> "Chandra Kalyanaraman [MSFT]" wrote:
|||it sure looks like the 'D. Bulk loading in identity type columns' sample in
the docs is doing something closely similar (but in my case, I need to
populate more than the key values into a table).
thanks, so much
rob
"Chandra Kalyanaraman [MSFT]" wrote:

> Let me understand this correct..
> PRT_MET is a child table which generates the MetID using a auto identity and
> this has to be propagated to MetID field in PRT_PRT which is the parent?
> If this is the case, bulkload does cannot do this.
> it can be other way around, prt-prt to prt-met.
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
>
>