Membuat dan Membaca Berkas XML Menggunakan T-SQL pada SQLSERVER

Posted: 23 Februari 2012 in Database, Programming
Tag:, ,

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

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');

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

SELECT ( SELECT * FROM dbo.kontak
 FOR
 XML PATH('Contact'),
 TYPE
)
FOR XML PATH(''),
ROOT('ContactList')
GO

Akan dihasilkan berkas XML sebagai berikut :

<ContactList>
  <Contact>
    <id>1</id>
    <name>Javan</name>
    <address>Bandung</address>
    <phone>0813</phone>
    <email>javan@javan.com</email>
  </Contact>
</ContactList>

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

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)

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

SELECT * FROM javan.dbo.kontak;
id name address phone Email
1 Javan Bandung 0813 javan@javan.com
2 Javan 2 Bandung 2 08132 javan2@javan.com

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 :

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

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

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

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.

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)

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

<ContactList>
  <Contact>
    <id>1</id>
    <name>Javan 3</name>
    <address>Bandung 3</address>
    <phone>08133</phone>
    <email>javan3@javan.com</email>
  </Contact>
</ContactList>

Maka eksekusi prosedurnya adalah sebagai berikut (query 6) :

EXECUTE ReadFromXMLFile 'D:\','test.xml'

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

SELECT * FROM javan.dbo.kontak;
id name address phone Email
1 Javan Bandung 0813 javan@javan.com
2 Javan 2 Bandung 2 08132 javan2@javan.com
3 Javan 3 Bandung 3 08133 javan3@javan.com

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s