Diğer

T-SQL’de XML Veri Tipini Kullanmak

Günümüzde bir çok uygulama ve uygulama parçası (bileşeni) XML formatındaki veri ile çalışabilmektedir. Örnek olarak uygulama arayüzlerini düşünürsek, bildiğiniz gibi gerek sunucu taraflı gerekse istemci taraflı bir çok arayüz bileşeni XML formatında veri kümelerini kaynak olarak alıp gösterebilmektedir.  Bu bakımdan bir çoğumuz XML formatına aşinayız ve sıklıkla kullanıyoruz.

Eğer verilerimizi XML formatında kullanma ihtiyacı duyuyorsak, veri kaynağımızda satırlar ve kolonlar halinde duran verilerimizi XML formatına dönüştürür yada verimizi baştan XML formatında saklarız.

Bu gibi bir durumda benim tercihim eğer bahsi geçen veriye ilişkisel olarak ihtiyacım sıklıkla olmayacaksa veriyi XML formatında tutmak olmuştur. Bu sayede sorgularım bir çok ilişki içerme ihtiyacı duymadığından çok daha verimli çalışabilmiş ve uygulama arayüzünde veriyi ara bir işleme tabi tutmadan gösterme imkanı bulmuşumdur.

SQL Server 2000 XML formatında veri ile çalışmayı kolaylaştıracak çeşitli fonksiyonlar içermektedir. SQL Server 2000’in XML ile ilgili sağladığı destek ile ilgili bilgi almak istiyorsanız Microsoft kaynaklarında yer alan http://www.microsoft.com/technet/prodtechnol/sql/2000/evaluate/xmlsql.mspx adresine bakmanızda fayda var. SQL Server 2005 sürümünde ise XML’e verilen destek XML formatında bir veri tipi oluşturulması ile bir hayli artmış ve 2008 sürümünde de devam etmiştir.

Bu yazıda, SQL Server’da XML veri tipinden nasıl faydalanabileceğimizi göstermek için bir çok uygulamada ihtiyaç duyulan ve bir özellik olarak talep edilen “etiket(tag)” verilerini XML yardımı ile saklayıp işleyen tablo ve prosedürleri oluşturacağız. Ama öncelikle SQL Server ve XML veri tipi hakkında kısa bir bilgi vermekte fayda var.

XML Veri Tipi

SQL Server’da XML veri tipini kullanarak XML dokümanlarını ve parçalarını (fragment) saklayıp bu tipte veriler ile rahatlıkla çalışılabilir. Bunun için XML tipinde değişkenler ve tablo alanları oluşturulabilir. Burada göz önüne alınması gereken bir kısıt XML değişkeninin 2 GB’ı aşamayacağıdır.

Ayrıca XML tipindeki değişken ve alanlar ile birlikte XML şemaları da kullanılabilir. Bu durumda bir XML şamasını referans gösteren XML değişkeni veya alanı “typed” olarak nitelendirilir. Eğer XML örneğimiz bir şemayı referans olarak göstermiyorsa “untyped” olarak adlandırılır.

Bu ön bilgiden sonra örneğimize geçerek XML veri tipinin kullanımına bakalım.

“Blog” Yazıları Örneği

Aşağıdaki örnekte kullanıcılarına “blog” yazıları ekleme olanağı veren bir uygulamanın yazıları ve bu yazılarla ilişkili etiketleri (tag) tutmak için ihtiyaç duyacağı şema, tablo, fonksiyon ve prosedürleri oluşturarak XML veri tipinin kullanımını incelemeye çalışacağız.

“Blog” yazı örneğimiz yazılarla ilişkili etiketleri XML veri tipinde tutacaktır. Saklanacak olan verinin güvenirliliği açısından etiket yapımızın şemasını tanımlayarak XML tipindeki alanımızı “typed” olarak kullanmak yerinde olacaktır.

Bunun için “CREATE XML SCHEMA COLLECTION” kodu ile  VeriServisItemTags adında bir şema koleksiyonu oluşturacağız.

CREATE XML SCHEMA COLLECTION [dbo].[VeriServisItemTags] AS
N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:t="http://veriservis.com/XMLSchema" targetNamespace=”http://veriservis.com/XMLSchema” elementFormDefault="qualified">
  <xsd:element name="ItemTags">
    <xsd:complexType>
      <xsd:complexContent>
        <xsd:restriction base="xsd:anyType">
          <xsd:sequence>
            <xsd:element name="Tags" minOccurs="0">
              <xsd:complexType>
                <xsd:complexContent>
                  <xsd:restriction base="xsd:anyType">
                    <xsd:sequence>
                      <xsd:element name="ItemTag" minOccurs="0" maxOccurs="unbounded">
                        <xsd:complexType>
                          <xsd:complexContent>
                            <xsd:restriction base="xsd:anyType">
                              <xsd:sequence />
                              <xsd:attribute name="UserId" type="xsd:string" />
                              <xsd:attribute name="Published" type="xsd:boolean" />
                              <xsd:attribute name="Count" type="xsd:integer" />
                              <xsd:attribute name="Tag" type="xsd:string" />
                            </xsd:restriction>
                          </xsd:complexContent>
                        </xsd:complexType>
                      </xsd:element>
                    </xsd:sequence>
                  </xsd:restriction>
                </xsd:complexContent>
              </xsd:complexType>
            </xsd:element>
          </xsd:sequence>
        </xsd:restriction>
      </xsd:complexContent>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>'

 

Şema tanımında XML formatındaki her etiket “ItemTag” adını ile tanımlanmıştır. Her “ItemTag” ise UserId, Published, Count, Tag adlarında alanlar içermektedir.

“UserId” etiketi ilk ekleyen kullanıcıyı tanımlamak için gerekecek olan alandır. “Published” alanı ile bu etiketin arayüzde gösterilip gösterilmeyeceği bilgisini tutabiliriz. “Count” alanı bu etiketin kaç kere tekrar ettiği bilgisini tutacak. Aynı etiket bir kere daha eklenildiğinde yeni bir etiket (ItemTag) oluşturmaktansa “Count” alanının değeri bir arttırılır. Bu örnekte “UserId” ve “Published” alanları ile fazla uğraşmadan Tag ve Count alanlarına yoğunlaşacağız.

Şema oluşturulduğuna göre tabloyu oluşturmak için bir eksik kalmadı ve artık tablo oluşturulabilir. Bu aşağıdaki “CREATE TABLE” kodunu kullanılarak yapılabilir.

CREATE TABLE [dbo].[udtBlogMetin]( 
    [Id] [uniqueidentifier] NOT NULL, 
    [UserId] [uniqueidentifier] NOT NULL, 
    [Metin] [varchar](max) NOT NULL, 
    [Etiket] [xml](CONTENT [dbo].[VeriServisItemTags]) NOT NULL, 
CONSTRAINT [PK_udtBlogMetin] PRIMARY KEY CLUSTERED
( 
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[udtBlogMetin] ADD  CONSTRAINT [DF_udtBlogMetin_Id]  DEFAULT (newid()) FOR [Id]
GO

ALTER TABLE [dbo].[udtBlogMetin] ADD  CONSTRAINT [DF_udtBlogMetin_UserId]  DEFAULT (newid()) FOR [UserId]
GO

ALTER TABLE [dbo].[udtBlogMetin] ADD  CONSTRAINT [DF_udtBlogMetin_Metin]  DEFAULT (‘<ItemTags xmlns=http://veriservis.com/XMLSchema><Tags></Tags></ItemTags>’) FOR [Etiket]
GO

Yukarıdaki alışılmış “CREATE TABLE” fonksiyonunda üzerinde duracağım alan “Etiket” alanıdır. “Etiket” alanı blok yazılarına eklenen etiketleri(tag) saklayacak olan XML formatındaki veriyi barındıracaktır. Bunun için VeriServisItemTags adında, bir önceki kod bloğunda oluşturduğumuz şemada yer alan tanımlara uyulur. Bu alanda her değişiklik yapıldığında verinin yapısını VeriServisItemTags isimli şemaya göre kontrol edilir. Eğer bu yapıya uymayan bir veri bu alana yazılmaya çalışılırsa bir hata oluşur. Bu sayede verinin güvenilirliği sağlanmış olur.

Veri yapısını tamamladıktan sonra sıra etiket ekleme ve silme işlemlerimizi yapacağımız prosedürleri oluşturmaya geldi. Bunu için udfEtiketEkle ve udfEtiketSil isimleri ile tanımladığımız iki yardımcı fonksiyondan faydalanacağız. Bu fonksiyonları oluştururken XML veri tipi metotları olan nodes() ve value() metotlarını da incelemiş olacağız.

XML veri tipi metotları ile ilgili ayrıntılı bilgiye http://msdn.microsoft.com/en-us/library/ms190798(SQL.90).aspx adresinden ulaşabilirsiniz.

Etiketleri Eklemek

Blog yazıları örneğimizde var olan bir blog yazısına etiket veya etiketler eklemek için aşağıdaki udfEtiketEkle fonksiyonunu kullanacağız.

Fonksiyon SourceTags ve NewTags adında iki parametre almaktadır. Bu fonksiyon basit olarak toplama yapan bir fonksiyon olarak düşünülebilir. Yaptığı iş SourceTags ile belirtilen XML veri tipindeki etiketlere NewTags ile belirtilen XML veri tipindeki etiketleri eklemek ve sonucu döndürmek olacaktır.

CREATE FUNCTION [dbo].[udfEtiketEkle] 
( 
    @SourceTags XML, 
    @NewTags XML
) 
RETURNS XML
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX) 
    SET @Result = '<ItemTags xmlns="http://veriservis.com/XMLSchema"><Tags>' + CAST(( 
        SELECT '' AS UserId, 'true' AS Published, ISNULL(ItemTag.STag, N.NTag) AS Tag, ISNULL(ItemTag.SCount,0) + ISNULL(N.NCount,0) AS [Count] 
        FROM( 
            SELECT SourceTags.Tags.value('./@Tag','nvarchar(1000)') AS STAG, SourceTags.Tags.value('./@Count','int') AS SCount
            FROM @SourceTags.nodes('declare namespace d="http://veriservis.com/XMLSchema";/d:ItemTags/d:Tags/d:ItemTag') AS SourceTags(Tags) 
            ) ItemTag
        FULL OUTER JOIN( 
            SELECT NewTags.Tags.value('./@Tag','nvarchar(1000)') AS NTag, NewTags.Tags.value('./@Count','int') AS NCount
            FROM @NewTags.nodes('declare namespace d="http://veriservis.com/XMLSchema";/d:ItemTags/d:Tags/d:ItemTag') AS NewTags(Tags) 
            ) N ON N.NTag = ItemTag.STag
        FOR XML AUTO, TYPE
    ) AS NVARCHAR(MAX)) + '</Tags></ItemTags>'

RETURN CASE WHEN @Result IS NULL THEN ‘<ItemTags xmlns=”http://veriservis.com/XMLSchema”><Tags></Tags></ItemTags>’ ELSE CAST(@Result AS XML) END END GO

Şimdi bu fonksiyonu kullanarak bir yazı ve bu yazıya bağlı etiketleri nasıl ekleyeceğimizi görelim. Bunun için aşağıdaki kod bloğunu çalıştırmamız yeterli olacaktır. Koddaki SELECT cümlesi aracılığı ile sonucu görebiliriz.

INSERT INTO udtBlogMetin (Id, UserId, Metin, Etiket)

VALUES (NEWID(), NEWID(), 'Blog Text', 
        dbo.udfEtiketEkle('<ItemTags xmlns="http://veriservis.com/XMLSchema"><Tags></Tags></ItemTags>' , 
            '<ItemTags xmlns="http://veriservis.com/XMLSchema"><Tags>
                <ItemTag UserId="0001" Published="1" Count="1" Tag="Uygulama" />
                <ItemTag UserId="0001" Published="1" Count="1" Tag="Geliştirme" />
                <ItemTag UserId="0001" Published="1" Count="1" Tag="Net" />
            </Tags></ItemTags>' ))

SELECT * FROM udtBlogMetin

Bu işlemin çıktısı aşağıdaki gibi olacaktır.

 

 

Bundan sonraki adım olarak bu yazıya daha sonradan etiket ekleme ihtiyacı oluştuğunu düşünelim. Bunun için yine udfEtiketEkle fonksiyonundan yararlanarak aşağıdaki kod bloğunu kullanabiliriz.

UPDATE udtBlogMetin SET
    Etiket = dbo.udfEtiketEkle(Etiket, '<ItemTags xmlns="http://veriservis.com/XMLSchema"><Tags>
                <ItemTag UserId="0001" Published="1" Count="1" Tag="Uygulama" />
                <ItemTag UserId="0001" Published="1" Count="1" Tag="Geliştirme" />
            </Tags></ItemTags>')

WHERE Id = '180E0369-DF93-4FB8-B30E-8CC66664BB3B' 
SELECT * FROM udtBlogMetin

Bu işlemin çıktısı ise aşağıdaki gibi olacaktır.

 

 

 

 

Bir cevap yazın

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