Harun Özer


Proje veya dijital katalog yaptırmak için
iletişim formu doldurabilirsiniz..

<< c#.net nullable int | c#.net Extension method tanımlamak>>

t-sql ile kayıtları tek satır olarak almak ve For Xml explicit

Yazar harunozer 13 March 2012

t-sql ile kayıtları tek satır olarak almak ve For Xml explicit select in For XML anahtarından faydalanılarak t-sql sorgusundan dönen kayıtları tek satır olarak alabiliriz. For xml anahtarı adından da anlaşılacağı üzere aslında sorguyu xml formatında tek kayıt olarak almaya yarar.

t-sql ile bir sorgudan dönen kaydın bir alanını tek satır olarak almak için (benim bildiğim) 2 yol vardır. İlk yol gayet basit nvarchar tipinde bir değişken tanımlayıp dönen kaydı bu değişkene atayıp okumak, diğeri ise for xml explicit ile. İlk önce basit olan çözümü görelim.

 

declare @Ad as nvarchar(max)
set @Ad = ''
select 
       @Ad += ', ' + Isnull(Ad ,'') 
from Üye
 
select STUFF(@Ad, 1, 2, '')
 
Üye tablomuzun olduğunu varsayalım ve bu tablodaki isimleri aralarında ',' olacak sekilde tek bir kayıt olarak almak istiyor olalım. İlk önce nvarchar(max) tipinde bir değişken tanımlayıp '' atama yapıyoruz aksi halde null ile bir stringin toplanması null olacağından sonuç hep null olurdu. Sonra ise select ifademizi yazıyoruz bu select ifadesindeki @Ad += ',' + Isnull(Ad,'') tanımladığımız @Ad değişkenine kendi değerini select ifadesinden gelen Ad fieldının değeri ile birleştirerek ve araya ',' koyarak birleştiriyor. Tabiki bu şekilde @Ad değişkeninin başında fazladan ',' kalıyor.Bu fazlada ',' ü de select STUFF(@Ad, 1, 2, '') ile temizliyoruz. Yani @Ad daki 1. karakterden itibaren 2 karakteri '' ile değiştirmiş oluyoruz.

Kayıtları tek satır olarak almaya geçmeden önce for xml anahtarının nasıl kullanıldığına biraz gözatalım.

Standart olarak yazdığmız sql sorgusu default olarak FOR Browse anahtarı ile çalışır yani datayı satırlar ve sütunlar halinde verir.

Select Ad,Soyad from Kişi sorgusu  ile Select Ad,Soyad from Kişi FOR Browse sorgusu veriyi aynı şekilde verir. For XML anahtarı ise veriyi xml formatında verir. Şimdi bu sorgunun çıktısını  her field ın kendi adında elementin içinde olduğu xml formatında alalım.Bunun için;

Select Ad,Soyad from Kişi FOR XML PATH('')

Eğer her kaydın başka bir elementin altında gelmesi isteniyorsa PATH('') içinde verilir. Yukarıdaki select ifadesi aşağıdaki formatda xml çıktısı verir.

<Ad>beyaz</Ad>
<Soyad>taş</Soyad>
<Ad>zeki</Ad>
<Soyad>kaya</Soyad>
<Ad>bilal</Ad>
<Soyad>murat</Soyad>
 
Örneğin her kaydı Kisi elementi içinde almak için Path('Kisi') yazmak gerekir.Bu durumda çıktı aşağıdaki gibi olur.
 
<Kisi>
     <Ad>beyaz</Ad>
     <Soyad>taş</Soyad>
</Kisi> 
<Kisi> 
     <Ad>zeki</Ad>
     <Soyad>kaya</Soyad>
</Kisi>  
 
Bütün field ları belirteceğimiz bir elementin attirbute leri olarak almak istersek de Raw anahtarından faydalanabiliriz. 
 
Select Ad,Soyad from Kisi FOR XML raw('Kisi')
 
sorgusu xml çıktısını aşagıdaki gibi verir.
 
<Kisi Ad="beyaz" Soyad="taş" />
<Kisi Ad="zeki" Soyad="kaya" />
 
 
Şimdi en başta belittiğimiz konuya yani kayıtları tek satır olarak almaya gelelim. PATH in parametresini boş ('')  verip field isimlerinin de No Name gelmesini sağlayarak sorgudan gelen veriyi , ile ayrılmış bir şekilde alabiliriz.Aynen aşağıdaki gibi.
 
Select Ad + ',',Soyad + ',' from Kisi FOR XML PATH('')
 
İlişkili tabloların kayıtlarını ChildElement ler şeklinde almak için FOR XML in EXPLICIT anahtarı kullanılabilir.Örneğin [Kategori] ve [Alt Kategori] adında 2 tablo var ve [Alt Kategori] tablosundaki kayıtları Kategori elementinin child elementi olarak almak için aşağıdaki gibi UNION ile sorguları birleştirip TAG ve PARENT değerlerini kullanmak gerekecek.
 
SELECT
3 AS TAG 
, 0 AS PARENT 
,ID AS [kategori!3!id] 
,Kategori AS [kategori!3!ad]
,NULL as [altkategori!6!id] 
,NULL AS [altkategori!6!alt] 
FROM Kategori
 
UNION ALL
 
SELECT
6 AS TAG
,3 AS PARENT
,k.ID
,k.Kategori
,ak.ID 
,ak.[Alt Kategori] 
FROM [Alt Kategori] as ak
INNER JOIN Kategori as k  ON k.ID = ak.Kategori
 
ORDER BY [kategori!3!id],[altkategori!6!id]
FOR XML EXPLICIT;
 
TAG alanı select e verdiğimiz ID gibi düşünülebilir. Parent alanı ise bu selectin hangi selectin altında olduğu yani hangi elementin altında olacağını belirtir.
 
 [kategori!3!id]  formatındaki field alanlarındaki değerler sırasıyla Element name,değerin hangi tag sorgusundan geleceği ve attirbute ismi ni belirtir. En üst sorgu kategori olduğu için bu sorgu içine gelecek sorgularda ve onların alt sorgularda order by da belirtilen [kategori!3!id] field ı olmak zorundadır.
 
1. sorguda  [altkategori!6!id] ve [altkategori!6!alt] alanların değerleri alt sorgularda verileceği için null verilmiştir.Order by da ise element yapısı sırası bağlanan field lar verilmelidir.Yani 1. ve 2. sorgudaki k.ID alanına göre iki sorgudaki ilişki sağlanmıştır.
2 den fazla sorgu bunun gibi iç içe elementler halinde alınabilir. Aşağıdaki dosyada örnek db ve sorgular SQL Server 2008 formatında verilmiştir.
 

t-sql_ile_kayıtları_tek_satır_olarak_almak.rar

Tam Sayfa
Tags Sql Server, Xml,
Kategoriler xml, Sql Server
İşlemler
Bu Makale 20  Kez Beğenildi.
İsim :    
e-Mail :    
Web Site :  
İmage :  
Güvenlik Kodu :  

Güvenlik Resmi
Resmi Yenile
    
Yorum :  
Tasarım Desenleri ve Mimarileri