Anasayfa / Yazılım / SQL / OPENXML Kullanarak XML Dokümanları Verilere Dönüştürmek

OPENXML Kullanarak XML Dokümanları Verilere Dönüştürmek

MS-SQL’de OPENXML komutu ile XML dokümanlarındaki verileri bir rowset yani tablo tipinde verilere dönüştürebiliriz. Aslında burada yaptığımız işlem XML doküman içindeki verilerin parçalanarak, tablolara aktarılmasıdır. OPENXML kullanırken aynı zamanda iki komut daha kullanırız, bunlar sp_xml_preparedocument ve sp_xml_removedocument komutlarıdır.

sp_xml_preparedocument komutu XML dokümanı hafızaya yükleyen komuttur.Bu işlem SQL Server tarafından kullanılan bellek miktarının 8’de birini kaplayan bir işlem sürecidir, yani serverde oldukça fazla bellek alanında işlem yapar.

sp_xml_removedocument komutu ise XML dokümanını SQL server hafızasından (belleğinden) silmeye yarar. Çalıştırılacak yeri ise mutlaka transaction sonunda olmalıdır. Yani yapmak istediğimiz tüm işlemin sonucunda bu komutu kullanırız. Kullanmamız belleğin boşaltılması açısından faydalı olacaktır.

DECLARE @hdoc int
DECLARE @doc varchar(1000) = N'












';
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
FROM OPENXML(@hdoc, N'/Urunler/Urun');
EXEC sp_xml_removedocument @hdoc;

20142702

Sonuç olarak görüleceği üzere SQL server üzerinde XML tablosundan oluşturulan veriler tabloya dönüştürüldü. Bu tbalo üzerindeki kolon adları ise tanımlanmış durumda. Bu kolon isimleri SQL server tarafından önceden tanımlanarak önümüze sunuluyor.Yani bu aslında SQL Server’da XML verilerden tabloya dönüşüm yaparken tanımlanmış standart format. Belirli bir yaoı yani structure mevcut. Tabi bunlar illa ki bu şekilde kalacak diye bir kural yok 🙂 SQL Server bizim bunu kendimize göre özelleştirmemize imkan tanıyor. Bunun içinde SELECT deyimimiz de WITH deyimini kullanırız. Bu sefer üstteki sorgumu WITH ile kullanarak kendime göre özelleştirilmiş kolonları oluşturacağım.

DECLARE @hdoc int
DECLARE @doc varchar(1000) = N'












';
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
FROM OPENXML(@hdoc, N'/Urunler/Urun/Siparis/SiparisDetay')
WITH (MusteriID int '@MusteriID',
UrunID int '../@UrunID',
UrunAd varchar(30) '../../@UrunAd',
SiparisID int '@SiparisID',
SiparisTarihi varchar(30) '../@SiparisTarihi');
EXEC sp_xml_removedocument @hdoc;

2014030801

Görüleceği üzere bu sefer tablomda kendim kolon adlarını tanımladım. Bunun için /Urunler/Urun/Siparis/SiparisDetay “rowpattern” kullandım. Yani ilgili tanımlamalarımı kendim oluşturdum. Aynı şekilde WITH deyimi ile birlikte colpattern de oluşturduk. Genel kullanımı bu şekildedir.

Daha farklı yöntemler de mevcuttur, bu yöntemlerin global adları : attribute-centric mapping ve element-centric mapping olarak adlandırılır.

Aşağıda yeni bir sorgu oluşturup, sorguda attribute-centric mapping kullanalım:

DECLARE @hdoc int
DECLARE @doc varchar(1000) = N'


0001
Televizyon
3

12
4


';
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
FROM OPENXML(@hdoc, N'/Siparisler/Siparis', 1)
WITH (UrunID int, UrunAd varchar(30),
SatisID varchar(10), SiparisID int,MusteriID int);
EXEC sp_xml_removedocument @hdoc;

Bu sorguda Siparisler/Siparis şeklinde rowpattern oluşturduk. Kullanmış olduğumuz yöntem attribute-centric mapping yöntemidir. Ve sorgu sonucu dönen veride sadece SiparisID mevcuttur. SiparisID attribute değerinin geriye veri şeklinde dönmesinin sebebi de Siparis elementinin altında bir attribute olmasıdır. Diğerlerinde görüleceği üzere veri dönmüyor.Mesela örneğimize göre SiparisDetay elementinin altındaki MusteriID attribute değeri geri dönmüyor.

2014031101

Şimdi aynı sorguyu bu sefer element-centric mapping ile yazalım. Bakalım geriye nasıl bi veri dönecek?

DECLARE @hdoc int
DECLARE @doc varchar(1000) = N'


0001
Televizyon
3

12
4


';
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
FROM OPENXML(@hdoc, N'/Siparisler/Siparis', 2)
WITH (UrunID int, UrunAd varchar(30),
SatisID varchar(10), SiparisID int,MusteriID int);
EXEC sp_xml_removedocument @hdoc;

Değişen satır sadece şu;

FROM OPENXML(@hdoc, N'/Siparisler/Siparis', 2)

Sonucumuz aşağıdaki gibi:

2014031102

Bu sefer Siparis elementi altındaki SiparisID haricindeki attributeler veri olarak geriye değer döndürdü. SiparisDetay elementi altında yer alan attributelardan ise geriye değer dönüşü olmadı.

Peki bir XML dokümanındaki tüm elementler altında yer alan alt elementleri yani attribute diğer adı ile özellikleri nasıl veri olarak geriye döndürüceğiz?

Bunun için her sütuna mutlaka ColPatterns tanımlamamız gerekiyor. Son sorgumu aşağıdaki gibi düzenliyorum.

DECLARE @hdoc int
DECLARE @doc varchar(1000) = N'


0001
Televizyon
3

12
4


';
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
FROM OPENXML(@hdoc, N'/Siparisler/Siparis/SiparisDetay')
WITH (UrunID int '../UrunID', UrunAd varchar(30) '../UrunAd',
SatisID varchar(10) '../SatisID', SiparisID int '../@SiparisID',
MusteriID int 'MusteriID');
EXEC sp_xml_removedocument @hdoc;

Sonuç aşağıdaki gibidir:

2014031103

Böylelikle tüm veriyi geriye döndürebiliriz. Görüleceği üzere tüm verileri çekebilmem için hem ColPattern hem de RowPattern kullanmamız gerekiyor.

RowPattern örneğimizde Siparisler/Siparis/SiparisDetay oldu. Böylelikle XML dokümanında en küçük yani en içteki elementten en dıştaki elemente kadar tüm verileri okumamız sağlanıyor. Eğer alt elementlerden listelenmesi gereken element var ise bunu da @ ile belirtiyoruz.

2 yorum

  1. Merhabalar , siteniz ve örnekleriniz çok yararlı fakat bu konudaki örneklerinizde varchar tanımlı değişkenin içinde “buzdolabı” kullanıldığı için hata alıyorum. Benim hatayı görmem zaman aldı diğer arkadaşlar uğraşmasın diye paylaşmak istedim.
    İyi çalışmalar.

  2. Teşekkür ederim, bilgilendirmeniz için de ayrıca teşekkürler.

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir