Bulk Load Identity Propagation

Hello,
I'm working with the SQLXML3.0 Bulk Loader SP3 & I am having a problem
propagating identity columns. I can get the identity to propagate to one
child, but the grandchild ends up getting 0s in the column that I expect the
identity value to be in.
Has anyone else had this problem, or can any of you see what I'm doing
wrong?
TIA,
Aelk
Here is a snippet from my xsd:
<xs:annotation>
<xs:appinfo>
<sql:relationship name="Batch-Test"
parent="XMLDM_Batch"
parent-key="BatchID"
child="XMLDM_Test"
child-key="BatchID" />
</xs:appinfo>
</xs:annotation>
<xs:annotation>
<xs:appinfo>
<sql:relationship name="Test-Child2"
parent="XMLDM_Test"
parent-key="BatchID ID"
child="XMLDM_TestChild2"
child-key="BatchID Child2ID" />
</xs:appinfo>
</xs:annotation>
<xs:annotation>
<xs:appinfo>
<sql:relationship name="Child2-GrandChild"
parent="XMLDM_TestChild2"
parent-key="BatchID Child2ID"
child="XMLDM_TestGrandChild"
child-key="BatchID Child2ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="TEST" sql:relation="XMLDM_Test"
sql:relationship="Batch-Test" >
<xs:complexType>
<xs:sequence>
<xs:element name="CHILD2" minOccurs="0" maxOccurs="unbounded"
sql:relation="XMLDM_TestChild2" sql:relationship="Test-Child2">
<xs:complexType>
<xs:sequence>
<xs:element name="GRANDCHILD" minOccurs="0"
maxOccurs="unbounded" sql:relation="XMLDM_TestGrandChild"
sql:relationship="Child2-GrandChild" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
And here is a sample of my XML that I am trying to load:
<TEST>
<CHILD2>
<GRANDCHILD>my grandchild</GRANDCHILD>
</CHILD2>
</TEST>
I'm getting the following in my tables:
XMLDM_Test:
BatchID ID
30 6
XMLDM_TestChild2:
BatchID Child2ID
30 6
XMLDM_TestGrandChild:
BatchID Child2ID Grandchild
30 0 my grandchild
This looks like a limitation in SqlXml3 Sp3.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"aelk" <aelk@.discussions.microsoft.com> wrote in message
news:D9B0719F-89DF-437A-A06F-43D6257817F1@.microsoft.com...
> Hello,
> I'm working with the SQLXML3.0 Bulk Loader SP3 & I am having a problem
> propagating identity columns. I can get the identity to propagate to one
> child, but the grandchild ends up getting 0s in the column that I expect
> the
> identity value to be in.
> Has anyone else had this problem, or can any of you see what I'm doing
> wrong?
> TIA,
> Aelk
> Here is a snippet from my xsd:
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="Batch-Test"
> parent="XMLDM_Batch"
> parent-key="BatchID"
> child="XMLDM_Test"
> child-key="BatchID" />
> </xs:appinfo>
> </xs:annotation>
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="Test-Child2"
> parent="XMLDM_Test"
> parent-key="BatchID ID"
> child="XMLDM_TestChild2"
> child-key="BatchID Child2ID" />
> </xs:appinfo>
> </xs:annotation>
> <xs:annotation>
> <xs:appinfo>
> <sql:relationship name="Child2-GrandChild"
> parent="XMLDM_TestChild2"
> parent-key="BatchID Child2ID"
> child="XMLDM_TestGrandChild"
> child-key="BatchID Child2ID" />
> </xs:appinfo>
> </xs:annotation>
> <xs:element name="TEST" sql:relation="XMLDM_Test"
> sql:relationship="Batch-Test" >
> <xs:complexType>
> <xs:sequence>
> <xs:element name="CHILD2" minOccurs="0" maxOccurs="unbounded"
> sql:relation="XMLDM_TestChild2" sql:relationship="Test-Child2">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="GRANDCHILD" minOccurs="0"
> maxOccurs="unbounded" sql:relation="XMLDM_TestGrandChild"
> sql:relationship="Child2-GrandChild" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:sequence>
> </xs:complexType>
> </xs:element>
>
> And here is a sample of my XML that I am trying to load:
> <TEST>
> <CHILD2>
> <GRANDCHILD>my grandchild</GRANDCHILD>
> </CHILD2>
> </TEST>
> I'm getting the following in my tables:
> XMLDM_Test:
> BatchID ID
> 30 6
> XMLDM_TestChild2:
> BatchID Child2ID
> 30 6
> XMLDM_TestGrandChild:
> BatchID Child2ID Grandchild
> 30 0 my grandchild

bulk load from a stream

The docs for sqlxml bulk loader indicate that the execute method
supports a stream for the document to load.
This code works:
xslTransform.Load("map.xsl")
textWriter = New XmlTextWriter("out.xml", Nothing)
xslTransform.Transform(xmlRsp, Nothing, textWriter)
textWriter.Flush()
textWriter.Close()
With xmlBulkLoader
.ConnectionString = "provider=SQLOLEDB;..."
.ErrorLogFile = "bulkLoaderErrors.txt"
.KeepIdentity = False
.XMLFragment = True
.Execute("schema.xml", "out.xml")
End With
When the transform is sent into a memory stream, the stream fills as
expected
dim memStream as New System.IO.MemoryStream
xslTransform.Transform(xmlRsp, Nothing, memStream)
but I can't figure out how to use memStream in
xmlBulkLoader.Execute("schema.xml", memStream)
I've tried using various properties of the memStream, and tried using a
stream reader, but the execute always returns "Error opening data file."
Check out the article here:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
It shows how to do this in .NET
Irwin
<jmeerdink@.synergy.gs> wrote in message
news:1110571753.372547.264070@.f14g2000cwb.googlegr oups.com...
> The docs for sqlxml bulk loader indicate that the execute method
> supports a stream for the document to load.
> This code works:
> xslTransform.Load("map.xsl")
> textWriter = New XmlTextWriter("out.xml", Nothing)
> xslTransform.Transform(xmlRsp, Nothing, textWriter)
> textWriter.Flush()
> textWriter.Close()
> With xmlBulkLoader
> .ConnectionString = "provider=SQLOLEDB;..."
> .ErrorLogFile = "bulkLoaderErrors.txt"
> .KeepIdentity = False
> .XMLFragment = True
> .Execute("schema.xml", "out.xml")
> End With
> When the transform is sent into a memory stream, the stream fills as
> expected
> dim memStream as New System.IO.MemoryStream
> xslTransform.Transform(xmlRsp, Nothing, memStream)
> but I can't figure out how to use memStream in
> xmlBulkLoader.Execute("schema.xml", memStream)
> I've tried using various properties of the memStream, and tried using a
> stream reader, but the execute always returns "Error opening data file."
>
|||Thanks Irwin - wow that's a lot of code. The article mentioned that
ADODB.Stream is directly supported. Would that easier to work with?
|||That's in native code, looked like you were doing managed. If you were
doing native code it might be.
"jayMeer" <jmeerdink@.synergy.gs> wrote in message
news:1112792519.498993.310540@.z14g2000cwz.googlegr oups.com...
> Thanks Irwin - wow that's a lot of code. The article mentioned that
> ADODB.Stream is directly supported. Would that easier to work with?
>

bulk load from a stream

The docs for sqlxml bulk loader indicate that the execute method
supports a stream for the document to load.
This code works:
xslTransform.Load("map.xsl")
textWriter = New XmlTextWriter("out.xml", Nothing)
xslTransform.Transform(xmlRsp, Nothing, textWriter)
textWriter.Flush()
textWriter.Close()
With xmlBulkLoader
.ConnectionString = "provider=SQLOLEDB;..."
.ErrorLogFile = "bulkLoaderErrors.txt"
.KeepIdentity = False
.XMLFragment = True
.Execute("schema.xml", "out.xml")
End With
When the transform is sent into a memory stream, the stream fills as
expected
dim memStream as New System.IO.MemoryStream
xslTransform.Transform(xmlRsp, Nothing, memStream)
but I can't figure out how to use memStream in
xmlBulkLoader.Execute("schema.xml", memStream)
I've tried using various properties of the memStream, and tried using a
stream reader, but the execute always returns "Error opening data file."Check out the article here:
http://msdn.microsoft.com/library/d... />
sqlxml.asp
It shows how to do this in .NET
Irwin
<jmeerdink@.synergy.gs> wrote in message
news:1110571753.372547.264070@.f14g2000cwb.googlegroups.com...
> The docs for sqlxml bulk loader indicate that the execute method
> supports a stream for the document to load.
> This code works:
> xslTransform.Load("map.xsl")
> textWriter = New XmlTextWriter("out.xml", Nothing)
> xslTransform.Transform(xmlRsp, Nothing, textWriter)
> textWriter.Flush()
> textWriter.Close()
> With xmlBulkLoader
> .ConnectionString = "provider=SQLOLEDB;..."
> .ErrorLogFile = "bulkLoaderErrors.txt"
> .KeepIdentity = False
> .XMLFragment = True
> .Execute("schema.xml", "out.xml")
> End With
> When the transform is sent into a memory stream, the stream fills as
> expected
> dim memStream as New System.IO.MemoryStream
> xslTransform.Transform(xmlRsp, Nothing, memStream)
> but I can't figure out how to use memStream in
> xmlBulkLoader.Execute("schema.xml", memStream)
> I've tried using various properties of the memStream, and tried using a
> stream reader, but the execute always returns "Error opening data file."
>|||Thanks Irwin - wow that's a lot of code. The article mentioned that
ADODB.Stream is directly supported. Would that easier to work with?|||That's in native code, looked like you were doing managed. If you were
doing native code it might be.
"jayMeer" <jmeerdink@.synergy.gs> wrote in message
news:1112792519.498993.310540@.z14g2000cwz.googlegroups.com...
> Thanks Irwin - wow that's a lot of code. The article mentioned that
> ADODB.Stream is directly supported. Would that easier to work with?
>