SQLXMLBULKLOAD Delphi SQL2000 Binary
From: Shoban Pattam (shoban_at_hotmail.com)
Date: 03/26/04
- Next message: Bryant Likes: "Re: how to capture o/p of SELECT .. FOR XML AUTO."
- Previous message: Gopinath Munisifreddy: "how to capture o/p of SELECT .. FOR XML AUTO."
- Messages sorted by: [ date ] [ thread ]
Date: 26 Mar 2004 06:39:41 -0800
I have been having problems trying to use SQLXMLBULKLOAD SP 2 along
with Delphi 7 to Import and Export SQL2000 table data through
generation of XML/XSD files. I have been working on this issue for the
last 3 days and finally have been successful at cracking it.
This is a long post but I would like to keep things simple and clear.
Here goes :
Table structure is as follows
CREATE TABLE [dbo].[CMS_PDF] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PDF] [image] NULL ,
[Time_Stamp] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The XSD for the above table is as follows :
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ROOT" sql:is-constant="true" >
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="CMS_PDF"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="CMS_PDF" sql:relation="CMS_PDF" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name='ID' type="xsd:int"
sql:field='ID' sql:datatype="int"/>
<xsd:element name='PM' type="xsd:string"
sql:field='PM' sql:datatype="varchar"/>
<xsd:element name='PDF' type="xsd:base64Binary"
sql:field='PDF' sql:datatype="image"/>
<xsd:element name='Time_Stamp' type="xsd:dateTime"
sql:field='Time_Stamp' sql:datatype="datetime"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The XML for the above looks like this (only a snippet):
<CMS_PDF><ID>1</ID><PM>R61PI</PM><PDF>JVBERi0xLjQNJeLjz9MNCjEgMCBvYmoNPDwgDS9TIC9Ob3JtYWwgDS9DIC9Ob3JtYWwgDS9QZyAxNTYgMCBSIA0vSyAwIA0vUCA1NSAwIFIgDT4+IA1lbmRv
The Delphi Code is as follows :
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls,
Forms,
Dialogs, StdCtrls, DB, ADODB, ComObj;
type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
ADOStoredProc1: TADOStoredProc;
ADOQuery1: TADOQuery;
Button2: TButton;
procedure Button1Click(Sender: TObject);
procedure ADOConnection1Login(Sender: TObject; Username,
Password: String);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
uses SQLXMLBULKLOADLib_TLB, ADODB_TLB;
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
var
OutStream : Variant;
adoCmd : Variant;
begin
Screen.Cursor := crSQLWait;
try
if FileExists('C:\temp\CMS_PDF.xml') then
DeleteFile('C:\temp\CMS_PDF.xml');
adoCmd := CoCommand.Create;
OutStream := CoStream.Create;
adoCmd.ActiveConnection := ADOConnection1.ConnectionObject;
adoCmd.commandtext:='SELECT [ID], [PM], [PDF], [Time_Stamp] FROM
CMS_PDF FOR XML AUTO, ELEMENTS, BINARY BASE64';
OutStream.Open;
OutStream.LineSeparator := adCRLF;
adoCmd.Properties.Item['Output Stream'].Value := OutStream;
adoCmd.Execute(, , adExecuteStream);
OutStream.Position := 0;
OutStream.SaveToFile('C:\temp\CMS_PDF.xml', adPersistXML);
finally
Screen.Cursor := crDefault;
OutStream := varNull;
adoCmd := varNull;
end;
end;
procedure TForm1.ADOConnection1Login(Sender: TObject; Username,
Password: String);
begin
UserName := 'something';
Password := 'something';
end;
procedure TForm1.Button2Click(Sender: TObject);
var
SQL_BL : SQLXMLBulkLoad3;
wFile : variant;
begin
screen.Cursor := crSQLWait;
try
if not adoconnection1.Connected then
adoconnection1.Open;
SQL_BL := CoSQLXmlBulkLoad3.Create;
SQL_BL.ConnectionString:= adoconnection1.ConnectionString;
SQL_BL.ErrorLogFile:='c:\temp\BulkLoadErrors.log';
SQL_BL.BulkLoad := True;
SQL_BL.KeepIdentity := True;
SQL_BL.XMLFragment := True;
// Load the Application data
wFile:='C:\temp\CMS_PDF.xml';
if FileExists(wFile) then
SQL_BL.Execute('C:\temp\CMS_PDF.xsd',wFile);
finally
screen.Cursor := crDefault;
ShowMessage('Finished loading to database.');
end;
end;
end.
I hope this helps a few people who are having issues with
SQLXMLBULKLOAD to IMPORT XML files. Note : I have not used a ADO
generated XML file. By not using a ADO Generated XML file, I was able
to create a simple XSD file for the schema. To further understand what
I am saying about ADO Generated XML files please READ the article
"Using XML Bulk Load to Load ADO-Generated XML Data"
If anybody has any Q.s about the above implementation, feel free to
Post your Q.s.
- Next message: Bryant Likes: "Re: how to capture o/p of SELECT .. FOR XML AUTO."
- Previous message: Gopinath Munisifreddy: "how to capture o/p of SELECT .. FOR XML AUTO."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
