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?
>

Bulk load doesn't preserve element order?

Hi,
I have an interesing issue with sqlxml bulk load. It's about the order in which bulk load inserts rows in the database. I assumed that the rows should be inserted in the same order as the elements appear in the xml file, but looks like it is not always the case.
We use SQL server 2000 and SQLXML 3.0 SP 3. The files are large(2GB) and look something like this:
<Betalingskrav>
<Krav>
<BetalingsKravStatus><![CDATA[1]]></BetalingsKravStatus>
<FakturaDato><![CDATA[04.02.2006]]></FakturaDato>
<CustomerAgreementReference>
<![CDATA[12345689]]></CustomerAgreementReference>
....
<Samtaler>
<Samtale_Gruppeheader>
<SamtaleType><![CDATA[Mobilsamtaler]]></SamtaleType>
<Benevning><![CDATA[Abonnentnr]]></Benevning>
<Telefonnr><![CDATA[12 34 56 78]]></Telefonnr>
<Periode><![CDATA[Periode03.01.06-02.02.06]]></Periode>
<Etikett_Dest><![CDATA[Destinasjon/Operator]]></Etikett_Dest>
<Etikett_Oppr_nr><![CDATA[Oppringt nummer]]></Etikett_Oppr_nr>
<Etikett_SamtDato><![CDATA[Dato]]></Etikett_SamtDato>
<Etikett_Samtstart><![CDATA[Start]]></Etikett_Samtstart>
<Etikett_lngd><![CDATA[Samtalelengde]]></Etikett_lngd>
<Etikett_Kost><![CDATA[Kostnad]]></Etikett_Kost>
</Samtale_Gruppeheader>
<Samtale_Data>
<Dta_Dest><![CDATA[Tele2 GSM]]></Dta_Dest>
<Dta_Oppr_nr><![CDATA[12345678]]></Dta_Oppr_nr>
<Dta_SamtDato><![CDATA[02.01.06]]></Dta_SamtDato>
<Dta_Samtsstart><![CDATA[11:13]]></Dta_Samtsstart>
<Dta_lngd><![CDATA[ 0:00:37]]></Dta_lngd>
<Dta_Kost><![CDATA[0.81]]></Dta_Kost>
</Samtale_Data>
<Samtale_Data>
....
</Samtale_Data>
...
</Samtaler>
</Krav>
...
</Betalingskrav>

The schema (i list a shortened version):
<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="Krav_T2KravLinje"
parent="Betalingskrav"
parent-key="BetalingskravID"
child="T2BetalingskravLinje"
child-key="BetalingskravID" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="KravType">
<xsd:all>
<xsd:element name="BetalerID" default="9999" />
<xsd:element name="FakturaTypeID" default="0" />
<xsd:element name="MalID" default="0" />
<xsd:element name="BuntID" default="0" />
<xsd:element name="UtstederID" default="28" />
...
<xsd:element name="Samtaler" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Samtale_Gruppeheader" type="Samtale_GruppeheaderType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje" />
<xsd:element name="Samtale_Data" type="Samtale_DataType"
sql:relation="T2BetalingskravLinje" sql:relationship="Krav_T2KravLinje"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="MVATekst1" type="xsd:string" />
<xsd:element name="MVATekst2" type="xsd:string" />
...
</xsd:all>
</xsd:complexType>
<xsd:complexType name="Samtale_GruppeheaderType">
<xsd:sequence>
<xsd:element name="SamtaleType" type="xsd:string" />
<xsd:element name="Benevning" type="xsd:string" />
<xsd:element name="Telefonnr" type="xsd:string" />
<xsd:element name="Periode" type="xsd:string" />
<xsd:element name="Etikett_Dest" type="xsd:string" />
<xsd:element name="Etikett_Oppr_nr" type="xsd:string" />
<xsd:element name="Etikett_Samtant" type="xsd:string" />
<xsd:element name="Etikett_SamtDato" type="xsd:string" />
<xsd:element name="Etikett_Samtstart" type="xsd:string" />
<xsd:element name="Etikett_lngd" type="xsd:string" />
<xsd:element name="Etikett_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="Samtale_DataType">
<xsd:sequence>
<xsd:element name="Dta_Dest" type="xsd:string" />
<xsd:element name="Dta_Oppr_nr" type="xsd:string" />
<xsd:element name="Dta_Samtant" type="xsd:string" />
<xsd:element name="Dta_SamtDato" type="xsd:string" />
<xsd:element name="Dta_Samtsstart" type="xsd:string" />
<xsd:element name="Dta_lngd" type="xsd:string" />
<xsd:element name="Dta_Kost" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="Betalingskrav" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Krav" type="KravType" sql:relation="Betalingskrav" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
XML data represents telephone bills with a listing of all calls, sms's, data services and so on. Samtale_Gruppheader elements define headers, and Samtale_Data elements contain the items that go below the headings. The point is that the sequence of elements inside <Samtaler> must be preserved in the database via identity column to provide correct formatting of bills. Not a good approach, I understand, but we are dealing with a legacy system that is not easy to modify...
The shema above maps xml data to two tables in the database that have primary-foreign relationship. The foreign-key table(T2BetalingskravLinje) contains both contents of Samtale_Gruppeheader and Samtale_Data elements. When the bills are rendered, rows in this table are shown in the sequence given by the identity column, in other words in the order they were inserted into the table.
KeepIdentity property is set to false on the bulk load component, the SQL server generates identity keys itself. This works fine, foreign keys are propagated correctly. But the order of rows is not the same as the order of elements in <Samtaler>! Not all the times, but in about 5 to 10% of the cases and I don't see any pattern here. I extracted a <krav> element that had this problem from a real file, and tried to bulk load the resulting short file. The effect disappered, rows were in correct order...
So the question is: Is this behaiviour normal for bulk load component and the order of row insertion into foreign-key tables is not guaranteed to be the same as the order of elements in the xml file? Is it a bug or a feature? :-) Is there any way to enable order preservation?
Thanks in advance for any ideas or suggestions!

The bulkloading component on which the SQLXML bulkloader is built does not guarantee order. So the behaviour is normal.

Best regards

Michael

sql

Bulk Load - net enough memory error- HELP!

I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
database) . When I change the connection to point to another machine (simila
r
hardware configuration) I get
[There is insufficient system memory to run this query.]
The script is running on my machine connecting to a MSDE database on another
machine.
The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
still generates the error. I think it is a configuration issue because if I
change the connection to another development machine it works with the 140
meg file.Solution - I checked the maximum memory that the instance was set to. It was
5 meg, I increased it to 500 meg and the bulkload ran like a champ. it's a
good thing that you can use enterprise manager to manage MSDE databases....
.
"xke" wrote:

> I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
> database) . When I change the connection to point to another machine (simi
lar
> hardware configuration) I get
> [There is insufficient system memory to run this query.]
> The script is running on my machine connecting to a MSDE database on anoth
er
> machine.
> The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
> still generates the error. I think it is a configuration issue because if
I
> change the connection to another development machine it works with the 140
> meg file.
>sql

Bulk Load - net enough memory error- HELP!

I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
database) . When I change the connection to point to another machine (similar
hardware configuration) I get
[There is insufficient system memory to run this query.]
The script is running on my machine connecting to a MSDE database on another
machine.
The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
still generates the error. I think it is a configuration issue because if I
change the connection to another development machine it works with the 140
meg file.
Solution - I checked the maximum memory that the instance was set to. It was
5 meg, I increased it to 500 meg and the bulkload ran like a champ. it's a
good thing that you can use enterprise manager to manage MSDE databases.....
"xke" wrote:

> I have a script (SQLXML Bulk Load sp3) that runs fine on my machine (local
> database) . When I change the connection to point to another machine (similar
> hardware configuration) I get
> [There is insufficient system memory to run this query.]
> The script is running on my machine connecting to a MSDE database on another
> machine.
> The data file is 140 meg when I reduce it to 1 row (about 1k of data) it
> still generates the error. I think it is a configuration issue because if I
> change the connection to another development machine it works with the 140
> meg file.
>

Bulk insert a .NET dataset?

Hi,
Does it possible to insert an XML document generated from a Datset to SQL
Server using the SQLXML feature?
and this, without transforming the input document.
In my case this is the situation:
I've an oracle database, I'll develop a small tool that extract some data
fron this database using a VB.NET application.
From this query I'll generate a Dataset, then I'll save it as XML file.
This XML file is sent by FTP and here I want to upload these data to an SQL
Server databases.
My question is: can I upload these data without transforming the XML file?
Before I start my tests, I want to know your experience.
Thanks.
Jerome.
If you have a mapping for it, you can use bulkload to do this.
Alternativly, we also have the SqlXmlAdapter which can be used to persist
changes to a Dataset back to Sql Server.
Irwin Dolobowsky
Program Manager - SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:OEWujqmPEHA.2236@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does it possible to insert an XML document generated from a Datset to SQL
> Server using the SQLXML feature?
> and this, without transforming the input document.
> In my case this is the situation:
> I've an oracle database, I'll develop a small tool that extract some data
> fron this database using a VB.NET application.
> From this query I'll generate a Dataset, then I'll save it as XML file.
> This XML file is sent by FTP and here I want to upload these data to an
> SQL
> Server databases.
> My question is: can I upload these data without transforming the XML file?
> Before I start my tests, I want to know your experience.
> Thanks.
> Jerome.
>