p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_server_xml thread: Problem Using xmlbulkload to insert binary data to database


Message #1 by mathew2002it@y... on Wed, 3 Apr 2002 13:41:50
Hi Mathew,

I noticed two problems in the XSD file:

A.) The element book??
  <xsd:element name="Book" sql:relation="tblBooks" >
May be it's a copy-paste problem. Your XML file does not have Book
element, change it with, something like:
  <xsd:element name="Organization" sql:relation="tblOrganization" >

B.) Try base64Binary instead of Base64Binary 
Note the initial b is in lowercase.

After these changes, I tried the bulk load code from my article [1] and
it worked fine.

[1] http://www.PerfectXML.com/Articles/xml/importxmlsql.asp

Hope this helps.

Best wishes,
Darshan Singh
Managing Editor, PerfectXML
http://www.PerfectXML.com
mailto:darshan@P...




Now the data could be bulkload into the database but it's only successful 
for small image data. When i try to bulkload very large binary data ( the 
size of the photo is large for instances using bitmap ) using asp, error 
occured and error written on the browser is as follow:

Error Type:
XML SAX Parser (0x800703E9)
Recursion too deep; the stack overflowed. 

When i change all the photo into jpeg format, it works. Actually can we 
overcome this problem or is there any limit size of data that are allowed 
for using bulkload. 

Thanks,
Mathew


> Hi,
I>  am doing some research for my company on the technology of bulkload to 
i> nsert a pile of data into the database.

> Tis is the schema file which defines the mapping of the data to my 
d> atabase:-

> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 >            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

>   <xsd:element name="Book" sql:relation="tblBooks" >
 >   <xsd:complexType>
 >     <xsd:sequence>

>        <xsd:element name="Partner" type="xsd:string" 
sql:field="Company" />
 >       <xsd:element name="Address" type="xsd:string" />
 >       <xsd:element name="DateOfJoining" type="xsd:date" />

>        <xsd:element name="Signature" type="xsd:Base64Binary" 
s> ql:datatype="image" />

>      </xsd:sequence>
 >    </xsd:complexType>
 >  </xsd:element>
<> /xsd:schema>
-> ---------------------------------------------------------------------
B> elow is the xml file

> <?xml version="1.0"?>
<> Venture>
 >      <Organization>
 >            <Partner>Info-Trek</Partner>
 >            <Address>Malaysia</Address>
 >            <DateOfJoining>2/1/2002</DateOfJoining>
	>     <Signature xmlns:dt="urn:schemas-microsoft-com:datatypes" 
d> 
t:dt="bin.base64">R0lGODdhZAAeAPcAAAAAAICAgIAAAICAAACAAACAgAAAgIAAgICAQABA
Q> ACA/wBAgEAA/4BA 
A> P///8DAwP8AAP//AAD/AAD//wAA//8A////gAD/gID//4CA//8AgP+AQAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
A> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAAAAAAALAAAAABkAB4AAAj/AB0IHEiwoMGD 
C> BMqXMiwocOHECNKnEixosWLGDNq3Mixo8ePIEOKHEmypEABH1Ga1CigZcuOKlcqfNlQpUuW 
M> mfGZLiT5sWdOU/6dAgUKEWjK4dCLJoRKUmlEZlidCoSqlCqA41idXCzoMuuB7+GBVtzJley 
X> g2i/NoTLVeCMbv2VHuS6Na5CLW+zZrV6lyaL4sC5stz61nCYdUK7qtU7tC1aW0i3ps2r9jB 
e> SPTlTt27V/NPuMurkw5cF3LfSlPPmyZqWvVnr06xVxXbMK4kx/jhav18dvPcBcGxk1UKOOb 
k> mWn1nv2svLUhUs/jG1WNV/OpOneJs5bMtvpVlHzN1Y8fft47dYLu1W/Pn1Um8ibe04eFCRY 
7> 6up129qO/hv6rYZth9/gwk4YEjJzWfggRwFuGBCAQEAOw==</Signature>
 >      </Organization>
<> /Venture>
-> ------------------------------------------------------------------
W> hen the schema is loaded upon the xml in visual basic, i got this error

> Run-time error '-2147467259(80004005)' Undeclared XSD type: indicating 
u> nable to load the schema. 

> This is only happen when i want to insert the binary data into a image 
f> ield in the sql database. I read the SQLXML 3.0 Documentation and it 
s> tated that i should use (sql:datatype Annotation) in my schema in order 
t> o map the XML data type base64binary. If anyone knows the answer to 
this, 
p> lease help me.

> For those who wants to try bulkloading xml data into database, u can 
d> ownload and install

> i)Microsoft SOAP 2.0 Toolkit at 
h> ttp://www.microsoft.com/downloads/release.asp?
R> eleaseID=29247&area=search&ordinal=3 

> ii)SQLXML 3.0 at http://www.microsoft.com/downloads/release.asp?
R> eleaseID=35764&area=search&ordinal=3

> 
M> athew

  Return to Index