Blog

Langkah pertama adalah membuat relasi tabel ‘kontak’ beserta inisiasi data yang diperlukan (query 1) :

[source language="sql"]
CREATE TABLE kontak (
id int PRIMARY KEY IDENTITY,
name varchar(255) NOT NULL,
address varchar(255),
phone varchar(255),
email varchar(255)
)
INSERT INTO dbo.kontak (name,address,phone,email) VALUES (‘Javan’,'Bandung’,’0813′,’javan@javan.com’);
[/source]

Selanjutnya adalah membuat berkas XML menggunakan data dari tabel ‘kontak’ pada basisdata (query 2) :

[source language="sql"]
SELECT ( SELECT * FROM dbo.kontak
FOR
XML PATH(‘Contact’),
TYPE
)
FOR XML PATH(”),
ROOT(‘ContactList’)
GO
[/source]

Akan dihasilkan berkas XML sebagai berikut :

[source language="xml"]
<ContactList>
<Contact>
<id>1</id>
<name>Javan</name>
<address>Bandung</address>
<phone>0813</phone>
<email>javan@javan.com</email>
</Contact>
</ContactList>
[/source]

T-SQL juga memungkinkan untuk membaca XML dan menambahkannya ke tabel ‘kontak’ seperti yang ditunjukkan oleh query 3 berikut :

[source language="sql"]
DECLARE @MyXML XML
SET @MyXML = ‘<ContactList>
<Contact>
<name>Javan 2</name>
<address>Bandung 2</address>
<phone>08132</phone>
<email>javan2@javan.com</email>
</Contact>
</ContactList>’

INSERT INTO dbo.kontak (name,address,phone,email)
SELECT
a.b.value(‘Contact[1]/name[1]‘,’varchar(255)’) AS name,
a.b.value(‘Contact[1]/address[1]‘,’varchar(255)’) AS address,
a.b.value(‘Contact[1]/phone[1]‘,’varchar(255)’) AS phone,
a.b.value(‘Contact[1]/email[1]‘,’varchar(255)’) AS email
FROM @MyXML.nodes(‘ContactList’) a(b)
[/source]

Adapun data yang ada pada tabel kontak di basisdata adalah sebagai berikut (query 4) :

[source]
SELECT * FROM javan.dbo.kontak;
[/source]

Untuk membaca berkas (file) eksternal, maka perlu didefenisikan sebuah fungsi (readFileAsString) yang mengembalikan string s yaitu isi dari berkas yang dibaca. Defenisi fungsi terkait adalah sebagai berikut :

[source language="sql"]
CREATE FUNCTION [dbo].[readFileAsString] ( @Path VARCHAR(255), @Filename VARCHAR(100) )
RETURNS VARCHAR(max)
AS
BEGIN
– Definition
DECLARE @objFileSystem INT,
@objTextStream INT,
@objErrorObject INT,
@strErrorMessage VARCHAR(1000),
@command VARCHAR(1000),
@chunk VARCHAR(8000),
@string VARCHAR(max),
@hr INT,
@yesOrNo INT
– Algorithm
SELECT @string = ”
SELECT @strErrorMessage = ‘opening the File System Object’
EXECUTE @hr = sp_OACreate ‘Scripting.FileSystemObject’ , @objFileSystem OUT

IF @hr=0 SELECT @objErrorObject=@objFileSystem, @strErrorMessage=’Opening file "’+@path+’\'+@filename+’"’,@command=@path+’\'+@filename
– For Reading ASCII formatted
IF @hr=0 EXECUTE @hr = sp_OAMethod @objFileSystem , ‘OpenTextFile’, @objTextStream OUT, @command,1,false,0

WHILE @hr=0
BEGIN
IF @hr=0 SELECT @objErrorObject=@objTextStream,
@strErrorMessage=’finding out if there is more to read in "’+@filename+’"’
IF @hr=0 EXECUTE @hr = sp_OAGetProperty @objTextStream, ‘AtEndOfStream’, @yesOrNo OUTPUT

IF @yesOrNo<>0 break
IF @hr=0 SELECT @objErrorObject=@objTextStream,
@strErrorMessage=’reading from the output file "’+@filename+’"’
IF @hr=0 EXECUTE @hr = sp_OAMethod @objTextStream, ‘Read’, @chunk OUTPUT,4000
SELECT @String = @string + @chunk
END
IF @hr=0 SELECT @objErrorObject=@objTextStream, @strErrorMessage=’closing the output file "’+@filename+’"’
IF @hr=0 EXECUTE @hr = sp_OAMethod @objTextStream, ‘Close’
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source OUTPUT,@Description OUTPUT,@Helpfile OUTPUT,@HelpID OUTPUT
SELECT @strErrorMessage=’Error whilst ‘
+coalesce(@strErrorMessage,’doing something’)
+’, ‘+coalesce(@Description,”)
SELECT @string=@strErrorMessage
END
EXECUTE sp_OADestroy @objTextStream
– Fill the table variable with the rows for your result set
RETURN @string
END
[/source]

Untuk menggunakan fungsi di atas maka perlu meng-enable ‘OLE Automation Procedures’ (karena menggunakan system procedure sp_OA*) dengan menggunakan query 5 sebagai berikut :

[source language="sql"]
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
[/source]

Dengan memodifikasi query 3 dimana inisiasi nilai variabel string ‘MyXML’ diambil dari hasil pembacaan berkas xml oleh fungsi readFileAsString maka dapat didefinisikan procedure berikut yang bermanfaat untuk membaca berkas XML dengan format terdefenisi dan memasukkannya sebagai record baru pada tabel kontak.

[source language="sql"]
CREATE PROCEDURE ReadFromXMLFile
@FilePath VARCHAR (1024),
@FileName VARCHAR (1024)
AS
DECLARE @MyXML XML
SET @MyXML = dbo.readFileAsString (@FilePath, @FileName)
INSERT INTO dbo.kontak (name,address,phone,email)
SELECT
a.b.value(‘Contact[1]/name[1]‘,’varchar(255)’) AS name,
a.b.value(‘Contact[1]/address[1]‘,’varchar(255)’) AS address,
a.b.value(‘Contact[1]/phone[1]‘,’varchar(255)’) AS phone,
a.b.value(‘Contact[1]/email[1]‘,’varchar(255)’) AS email
FROM @MyXML.nodes(‘ContactList’) a(b)
[/source]

Sebagai contoh pemakaian prosedur, untuk berkas XML di ‘D:\test.xml’ sebagai berikut :

[source language="xml"]
<ContactList>
<Contact>
<id>1</id>
<name>Javan 3</name>
<address>Bandung 3</address>
<phone>08133</phone>
<email>javan3@javan.com</email>
</Contact>
</ContactList>
[/source]

Maka eksekusi prosedurnya adalah sebagai berikut (query 6) :

[source language="sql"]
EXECUTE ReadFromXMLFile ‘D:\’,'test.xml’
[/source]

Adapun data terakhir yang ada pada tabel kontak di basisdata adalah sebagai berikut (query 7) :

[source language="sql"]
SELECT * FROM javan.dbo.kontak;
[/source]