XML

SQL Server 2005 - Hızlı El Kitabı

XML

Log kayıtlarını tutuabilmek amaçlı bir tablo oluşturup bütün uygulamalarımızın log bilgilerini bu tabloda tutalım.Yalnız bilgileri normal text olarak değil xml olarak tutalım ki verilerimizi ilişkisel bir biçimde gruplandırabilelim.

Şimdi Log isminde bir tablo oluşturralım;

CREATE TABLE Log

(

Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

Aciklama XML

)

Şimdi ise veriyi alıp bu tabloya ekleyen bir stored procedure yazalım;

CREATE PROCEDURE LogEkle(@aciklama XML)

BEGIN

INSERT INTO Log VALUES(@aciklama)

END

Bu store procedure’e veri göndermke için ise şu T-SQL kodlarını yazıyoruz;

DECLARE @aciklama XML

SET @aciklama = '<log><app>Lig</app><aciklama>Takým Eklenemedi</aciklama>

<tarih>15.12.2007</tarih></log>'

EXEC LogEkle @aciklama

Bu durumda kayıt tabloya eklenecektir.Eğer XML verisi hatalı olur ise,exception fırlatılacak ve kayıt eklenmeyecektir.Girilen XML dökümanının doğruluğunu SQL Server kontrol etmektedir.

· XML dökümanın derinliği 128 en fazla seviye olabilir.

· XML dökümanı en fazla 2 GB olabilir.

XML Schema

XML dökümanı için bir biçim(format) belirlemek ve onun dışındaki verileri kabul etmemek,yani girilen XML dökümanın bazı kriterlere uygun olmasını sağlama(zorlamak) amaçlı XML Schema’lar kullanılır.

Yukarıdaki örnekte Aciklama alanına girilebilecek olan XML biçimini belirleyen bir schema tanımı yapalım.

Not: Schema’lar veri tabanı düzeyinde uygulanır ve saklanır.

CREATE XML SCHEMA COLLECTION LogSchema AS

'<schema xmlns="http://www.w3.org/2001/XMLSchema">

</sequence>

</complexType>

</element>

</schema>'

Bu tanımla birlikte, xml dökümanı içerisinde root altında log isminde bir tag olmalı,onun içinde app ve Aciklama isminde,string tipinde değer alan iki alt tag olmalıdır demiş oluyoruz.

Var olan bir XML Schema’yı silmek için şu komutu kullanıyoruz;

DROP XML SCHEMA COLLECTION LogSchema

Şimdi isterseniz bu XML Schema tanımına uygun bir XML değişkeni tanımlayalım;

DECLARE @aciklama XML(LogSchema)

SET @aciklama = '<log><app>Lig</app><aciklama>Log mesajı</aciklama></log>'

Böyle bir tanımlama hata vermeycektir.Çünkü XML değeri,Schema’ya uygun.Faka tbu tanıma uymayan bir xml değeri(xml formatına uygun bile olsa) hatalı olacak ve exception fırlatacaktır. Örneğin yukarıdaki tanımlamayı şu şekilde değiştirirsek hata alacağız;

DECLARE @aciklama XML(LogSchema)

SET @aciklama = '<log><app>Lig</app><detay >Log mesajı</detay></log>'

Doğrudan SQL Server içinde Schema tanımlamak yerine,bu tanımı bir dosyaya koyup,dosyadan yüklemek daha anlamlıdır.Yukarıdaki schemayı,xsd uzantılı bir dosyaya kaydettikten sonra,şu şekilde yükleyebiliriz;

DECLARE @schema XML

SELECT @schema = c FROM OPENROWSET(BULK 'C:\log.xsd',SINGLE_BLOB) AS TEMP(c)

CREATE XML SCHEMA COLLECTION LogSchema AS @schema

ÖRNEK: Mail bilgilerinin tutulduğu bir tablo yapalım ve bu tabloda Detay isimli bir kolon olsun ve türü XML olsun.Bu kolona özgü bir adet xml schema tanımlayalım.Detay kolonunda maile ait to,from,subject ve body kısımları olacaktır.

Schema oluşturalım;

CREATE XML SCHEMA COLLECTION MailSchema AS

'<schema xmlns="http://www.w3.org/2001/XMLSchema">

</sequence>

</complexType>

</element>

</schema>'

Tablomuzu oluşturalım;

CREATE TABLE Mail

(

Id INT IDENTITY(1,1) NOT NULL,

Detay XML(MailSchema) NULL,

CONSTRAINT PK_Mail PRIMARY KEY(Id)

)

Tabloya kayıt ekleyelim;

INSERT INTO Mail VALUES(

'<mail>

<to>Ahmet</to>

<from>Mehmet</from>

<subject>Toplantı Var</subject>

<body>Salı günü saat 15:30''da toplantımız var</body>

</mail>')

ÖRNEK: Kişiye ait Id,Ad,Soyad,Yas,Sehir,Semt ve AdresDetay bilgilerini tutmak istiyoruz.

<Ad>Ahmet</Ad>

<Adres>

<Sehir>Ankara</Sehir>

<Semt>Öveçler</Semt>

<Detay>4.Cadde</Detay>

</Adres>

</Kisi>

Bu tipte bir kaydı kaydedebilecek bir tablo yapısı ve xml schema oluşturalım;

XML Sxhema;

CREATE XML SCHEMA COLLECTION KisiSchema As

'<schema xmlns="http://www.w3.org/2001/XMLSchema">

</sequence>

</complexType>

</element>

</sequence>

</complexType>

</element>

</schema>'

Tablo;

CREATE TABLE Kisi

(

ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

Bilgi XML(KisiSchema)

)

Kayıt Ekleyelim;

INSERT INTO Kisi VALUES(

'<Kisi Id="5">

<Ad>Ahmet</Ad>

<Adres>

<Sehir>Ankara</Sehir>

<Semt>Öveçler</Semt>

<Detay>4.Cadde</Detay>

</Adres>

</Kisi>')

Tablodaki Veriyi XML’e Dönüştürme

Tablolardaki verileri FOR XML ifadesi ile XML formatına dönüştürebiliriz.

FOR XML RAW

Product tablosunu XML formatında listeleyelim;

SELECT ProductID,[Name],Color,ListPrice

FROM Production.Product

FOR XML RAW('Product'),ELEMENTS

Sonuç :

<Name>Adjustable Race</Name>

</Product>

Kolon isimlerini değiştirmek için AS ifadesiyle kolonları değiştirebiliriz.Element biçiminde değilde Attribute biçiminde yapmak istersek bu durumda ELEMENTS ifadesini kaldırmamız gerekir.Şimdi sorgumuzu şu şekilde değiştirelim;

SELECT ProductID AS ID,

[Name] AS UrunAdi,

Color AS Renk,

ListPrice ListeFiyati

FROM Production.Product

FOR XML RAW('Urun')

Sonuç:

· FOR XML RAW ile içiçe tag’lar konulamaz.

· Sadece 1 seviyede hiyerarşi oluşturabiliriz.

· Kök tag’ı yoktur.Dolayısıyla üretilen XML dökümanı Well-Formed değildir.

FOR XML AUTO

İçiçe tag’lar içeren XML dökümanı üretmek amaçlı kullanılır.

ÖRNEK: Her bir modeli ve o model altındaki ürünleri göstren bir XML dökümanı oluşturalım.

SELECT Model.[Name],Product.ProductID,Product.[Name],Product.Color

FROM Production.Product Product,Production.ProductModel Model

WHERE Product.ProductModelID=Model.ProductModelID

FOR XML AUTO,ELEMENTS

Sonuç:

<Model>

<Name>HL Road Frame</Name>

<Name>HL Road Frame - Black, 58</Name>

<Color>Black</Color>

</Product>

<Name>HL Road Frame - Red, 58</Name>

</Product>

</Model>

ÖRNEK: Ürünlerin kategorilerini ve modellerini XML olarak listeleyelim;

SELECT PC.[Name],PSC.[Name],P.ProductID,P.[Name],PM.[Name],P.Color,P.ListPrice

FROM Production.Product P,Production.ProductSubcategory PSC,

Production.ProductCategory PC,Production.ProductModel PM

WHERE P.ProductSubcategoryID=PSC.ProductSubcategoryID AND

PSC.ProductCategoryID=PC.ProductCategoryID AND

P.ProductModelID=PM.ProductModelID

ORDER BY PC.[Name],PSC.[Name],P.ListPrice

FOR XML AUTO,ELEMENTS

Sonuç:

<PC>

<Name>Accessories</Name>

<PSC>

<Name>Bike Racks</Name>

<P>

<Name>Hitch Rack - 4-Bike</Name>

<PM>

<Name>Hitch Rack - 4-Bike</Name>

</PM>

</P>

</PSC>

...

</PC>

FOR XML AUTO üzerine notlar;

· Kök tag’ı yoktur,o yüzden well-formed değildir.

· Her bir kolon aynı biçimde formatlanabilir,farklı farklı formatlanamaz.

· Yeni bir isim verilemez.FOR XML RAW’da isim verebiliyorduk.

FOR XML PATH

SQL Server 2005 ile gelen yeni bir özelliktir.İçiçe tag’lar oluşturmamıza izin verir ve her bir kolon için ayrı ayrı özellik belirtebiliriz.

ÖRNEK: Modeller ve modellere ait ürünlerin bilgilerini listeleyelim;

SELECT Model.[Name] '@ModelName',

Product.ProductID 'Product/Id',

Product.[Name] 'Product/Name',

Product.Color 'Product/Color',

Product.ProductNumber 'Product/Detail/ProductNumber',

Product.Size 'Product/Detail/Size',

Product.StandardCost 'Product/Detail/StandartCost'

FROM Production.Product Product,Production.ProductModel Model

WHERE Product.ProductModelID=Model.ProductModelID

FOR XML PATH('Model')

Sonuç:

<Name>HL Road Frame - Black, 58</Name>

<Color>Black</Color>

</Detail>

</Product>

</Model>

ÖRNEK: SubCategory ve bu kategori altındaki ürünleri şu formatta listeleyiniz;

Biçim:

<Name>HL Road Frame - Black, 58</Name>

<Color>Black</Color>

</Product>

</Kategori>

T-SQL Cümlesi:

SELECT PSC.[Name] '@Name',

P.ProductID 'Product/@Id',

P.[Name] 'Product/Name',

P.Color 'Product/Color',

P.ListPrice 'Product/Diger/@ListPrice'

FROM Production.Product P,Production.ProductSubcategory PSC

WHERE P.ProductSubcategoryID=PSC.ProductSubcategoryID

FOR XML PATH('Kategori')

FOR XML PATH Üzerine Notlar;

· Kök tag’ı sağlamadığından well-formed değildir.

· Tag’lar her bir satır için tekrarlanır.

· Yazılımcı, kolonlar üzerinde çok fazla kontrole sahiptir

Şimdiye kadar ki bütün modellerde kök tag’ı eklenemiyordu.Şimdi her bir modelde kullanılabilecek olan bir özelliği kullanarak,oluşturulacak XML dökümanına kök(root) ekleyelim.

SELECT ProductID AS ID,

[Name] AS UrunAdi,

Color AS Renk,

ListPrice ListeFiyati

FROM Production.Product

FOR XML RAW('Urun'), ROOT('Urunler')

Sonuç:

</Urunler>

NULL Değerleri Yönetmek

Gördüğümüz bu komularda,eğer bir kolondaki değer NULL ise,SQL Server,XML dökümanına bunu eklemiyordu.Şöyle bir SQL cümleciğini düşünelim;

SELECT 100 'kolon1',

200 'kolon2',

NULL 'kolon3',

400 'kolon4'

FOR XML RAW,ELEMENTS

Bu sorgu sonucunda şöyle bir değer dönecektir;

<row>

</row>

Görüldüğü gibi NULL olan değerleri almamıştır.Şimdi T-SQL’imizi şu şekilde değiştirelim;

SELECT 100 'kolon1',

200 'kolon2',

NULL 'kolon3',

400 'kolon4'

FOR XML RAW,ELEMENTS XSINIL

Sonuç:

</row>

Not:

Normalde FOR XML komutları geriye bir string ifade döndürmektedir.Dönen değer ise aslında bir XML dökümanıdır(ROOT kullanıyor ise).Madem ki geriye XML dönüyor,o zaman ben bu XML değerini strning olarak değilde,XML olarak almak istiyor isem bu durumda şu kodu kullanmalıyım;

DECLARE @myXML XML

SET @myXML =

(SELECT 100 'kolon1',

200 'kolon2',

NULL 'kolon3',

400 'kolon4'

FOR XML RAW,ELEMENTS XSINIL,TYPE)

ÖRNEK: Her bir alt kategoriyi ve alt kategoriler altındaki ürünleri listeleyiniz.Biçimi aşağıdaki gibi olacaktır;

<Name>Bib-Shorts</Name>

</products>

</Category>

...

</AllCategories>

Cevap:

SELECT [Name],

(SELECT ProductID,[Name],Color,ListPrice

FROM Production.Product P

WHERE P.ProductSubcategoryID = PSC.ProductSubcategoryID

FOR XML RAW('Product'),TYPE) as products

FROM Production.ProductSubcategory PSC

FOR XML RAW('Category'),ELEMENTS,ROOT('AllCategories')