Makale Özeti

Bundan önceki yazımda sizlere SQL Serverdan veriyi XML formatında web üzerinden yayınlamak için IIS üzerindeki gerkli ayarlamaları ve SQL cümleciğini İnternet Explorer üzerinden nasıl çalıştırcağımızı anlatmıştım.

Makale

SQL Serverdan Veriyi XML Olarak WEB Üzerinden Nasıl Sunarız  XML

SQL Serverdan Veriyi XML Olarak WEB Üzerinden Nasıl Sunarız? - 2


Bundan önceki yazımda sizlere  SQL Serverdan veriyi XML formatında web üzerinden yayınlamak için IIS üzerindeki gerkli ayarlamaları ve SQL cümleciğini İnternet Explorer üzerinden nasıl çalıştırcağımızı anlatmıştım. Bu yazımda bir Store Procedure kullanarak veriyi XML olarak yayınlamayı, sorgu şablonlarını ve bu yayınlanan veriyi XSL şablonu kullanımını göstermeye çalışacağım.


İnternet Explorer Kullanarak bir Store Procedure Nasıl Çalıştırılır?

Store Procedure(sp)  çalıştıracaz ancak hangi spyi diyebilirsiniz. Ozaman gelin önce bir sp yazalım sonra bunu Internet Explorerdan çağıralım.
Sp yazmak için birden fazla yöntem olabilir. Ama ben Query Analyzerı tercih ediyorum.
  1. Query Analyzerı calıştırın ve çalışmak istediğiniz SQL Servera  bağlanın.
  2. Açılan pencereye aşağıdaki SQL cümleciğini yazıp F5 tuşuna basın.
    USE
    Northwind
    GO
    CREATE PROCEDURE
    rk_xmlCatalog
    AS
    SELECT

    Category.CategoryName AS Category,
    product.productName AS Product,
    Product.UnitPrice AS Price
    FROM
    Categories Category
    INNER JOIN Products Product
    ON Category.CategoryID = Product.CategoryID
    ORDER BY
    Category.CategoryName,
    Product.ProductName
    FOR XML AUTO
    GO
  3. Daha sonra Internet Explorerı açın ve adres çubuğuna aşağıdaki adresi yazıp.
    http://localhost/xmltest?sql=EXECUTE+rk_XMLCatalog&root=root
  4. İmleç hala adres çubuğunda iken ENTER tuşuna basın.
Yukarda yazılanları sırası ile doğru bir şekilde yapmış iseniz Northwind databaseindeki ürünleri katagori bazında sıralı olarak şu şekilde görünecektir.
 
  <?xml version="1.0" encoding="utf-8" ?>
- <root>
- <Category Category="Grains/Cereals">
  <Product Product="Filo Mix" Price="7" />
  <Product Product="Gnocchi di nonna Alice" Price="38" />
  <Product Product="Gustafs Knäckebröd" Price="21" />
  <Product Product="Ravioli Angelo" Price="19.5" />
  <Product Product="Singaporean Hokkien Fried Mee" Price="14" />
  <Product Product="Tunnbröd" Price="9" />
  <Product Product="Wimmers gute Semmelknödel" Price="33.25" />
  </Category>
....
  </root>

Şimdi bu Sp tüm katagorilerdeki ürünleri getirdiğinden bu işlem maliyetlidir. Bunun yerine bu spye dışardan bir parametre ile istediğimiz katagorideki ürünleri getirmesini sağlayalım. Bu yöntem hem network trafiği hemde zaman bakımında daha karlı bir yöntem olacaktır.
 
  1. Aşağıdaki SQL Cümleciğini Query Analyzerda yazıp F5 ile çalıştırın.

    ALTER PROCEDURE rk_xmlCatalog
    @CategoryName NVARCHAR(30) = %
    AS
    SELECT

    Category.CategoryName AS Category,
    product.productName AS  Product,
    Product.UnitPrice AS Price
    FROM
    Categories Category
    INNER JOIN Products Product
    ON Category.CategoryID = Product.CategoryID
    WHERE
    Category.CategoryName LIKE @CategoryName
    ORDER BY
    Category.CategoryName,
    Product.ProductName
    FOR XML AUTO
     
  2. Internet Explorerin adres çubuğuna aşağıdaki adresi yazıp ENTER tuşuna basınız.
    http://localhost/xmltest/?sql=EXECUTE+rk_xmlCatalog+@CategoryName="Confections"&root=root

Sonuç olarak sadece Confections kategorisindeki ürünler gelecektir.
 

<?xml version="1.0" encoding="utf-8" ?>
- <root>
- <Category Category="Confections">
  <Product Product="Chocolade" Price="12.75" />
  <Product Product="Gumbär Gummibärchen" Price="31.23" />
  <Product Product="Maxilaku" Price="20" />
  <Product Product="NuNuCa Nuß-Nougat-Creme" Price="14" />
  <Product Product="Pavlova" Price="17.45" />
  <Product Product="Schoggi Schokolade" Price="43.9" />
  <Product Product="Scottish Longbreads" Price="12.5" />
  <Product Product="Sir Rodneys Marmalade" Price="81" />
  <Product Product="Sir Rodneys Scones" Price="10" />
  <Product Product="Tarte au sucre" Price="49.3" />
  <Product Product="Teatime Chocolate Biscuits" Price="9.2" />
  <Product Product="Valkoinen suklaa" Price="16.25" />
  <Product Product="Zaanse koeken" Price="9.5" />
  </Category>
  </root>

XML Şablon Kullanımı.

Şu ana kadar sunduğum SQL Cümlecikleri aslında basit sorgu cümleleri idi.  Birden fazla tablo üzerinde sorgu cekmek istediğinizde sorguların karmaşık bir hal aldığını ve adres çubuğundan bu sorguların girmenin zorlukları ile karşılaşacaksınız. Ayrıca sorgularınız karmaşık olmasa bile adres çubuğundan sizin yazdığınız bir sorgu cümlesinin görünmesini istemeyebilirsiniz. Bunu XML şablonları kullanarak yapabiliriz. Bu şablonlar içersinde birden fazla SQL cümleceği yazabileceği gibi X-Path sorgularda yazabilirsiniz. Bu şablonlar iyi formlanmış XML (well-formed XML) olmalıdır. XML şablon kullanmak için IISin XML şablon kullanımına göre konfigüre edilmesi gerekir. Gelin IISi bu işlem için konfigüre edelim.

  1. Start menüsünden Programs -> Microsoft SQL Server -> Configure SQL XML Support in IIS programını çalıştırın.
  2. Sunucunuzun adı üzerie gelip çift tıklayın ve Default Web Site üzerine tıklayın.
  3. Sağ tarafdaki pencerede xmltest üzerinde sağ-clikleyin ve açılan menüden propertiesi tıklayın.
  4. Açılan pencerede Settings sekmesini açın ve Allow template queriesin seçili olduğunu kontrol edin.
  5. Virtual Names sekmesinde New butonuna tıklayın açılan pencerede Virtual Namee templates, Typea combodan template, Pathde C:\Inetpub\wwwroot\xmltest yazınız.
  6. Save butonuna basıp ayarları kaydetin. Son olarakda Ok butonuna basıp Properties penceresini kapatın.

Şimdi IISimizi konfigüre ettik. Bundan sonraki adımda da XML şablon kullanarak Internet Explorerdan nasıl sonuç alacağımıza bakalım.

  1. Aşağıdaki kodu herhangi bir text editöre kopyalayın.
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:query>
    SELECT
    SupplierID,
    CompanyName,
    ContactName,
    Phone
    FROM
    suppliers
    ORDER BY
    CompanyName
    FOR XML AUTO
    </sql:query>
    </ROOT>
  2. Dosyayı C:\Inetpub\wwwroot\xmltest dizinine suppliers.xml adında kaydetin.
  3. Internet Explorerı açın ve adres çubuğunda aşağıdaki adresi yazıp ENTER tuşuna basın.
    http://localhost/xmltest/templates/suppliers.xml

Eğer herşeyi doğru yaptıysanız istediğiniz sorgu cümleciğinin result seti xml olarak görünecektir. Bunu ilk denediğimde şaşkınlıktan küçük dilimi yutacaktım. Gercekten çok hızlı ve çok kullanışlı bir yöntem. Böyle bir özellik olmasaydı bu işi yapmak için yüzlerce satır kod yazmak zorunda kalacaktık ve belkide bu satırları Microsoft kadar efektiv yazamayacağımızdan daha yavaş çalışacaktı.

Bu makalenin 1. bölümünde SELECT yapısında kullanılan FOR XML RAW ve FOR XML AUTO modlarını anlatmıştık. Bundan sonraki adımda FOR XML EXPLICIT Modunu alatacağım.

EXPLICIT Modunun Kullanımı

EXPLICIT modu sorgular için ek bir karmaşıklık daha getirir fakat sorguların üretmiş olduğu XMLin formatı üzerinde daha fazla kontrolun bizde olduğu moddur. EXPLICIT mod, sorgulamanın universal tablo formatında bir result set üretmesine gereksinim duyar. Bu universal tablo XML sonuçunu tümüyle tanımlandığı tablodur.

Aşağıdaki tablo Northwind databaseindeki Products, Categories ve ürün detayları tablolarındaki fiyat ve tedarikcisi bilgilerinin nasıl bir mantıksal yapıda olacağının bir örneğidir.
 

Tag Parent Category!1 !name Product!2 !Name Product!2 !Supplier!Element Product!2 !Price!Element
1 NULL Beverages NULL NULL NULL
2 1 NULL Chai Exotic Liquids 80
2 1 NULL Chang Exotic Liquids 19
1 NULL Condiments NULL NULL NULL
2 1 NULL Aniseed Syrup Exotic Liquids 10

Kolon adları genel XML tanımlarına uygun olarak belirlenmeli. Örneğin burada Türkçe  karekter kullanılmamlı aksi halde düzgün çalışmaz. EXPLICIT mod hakkında ayrıntılı bilgi almak için http://www.windowswebsolutions.com/Articles/Index.cfm?ArticleID=19810 adresinden faydalanabiilirsiniz. Şimdi EXPLICIT moda bir kaç örnek verelim.

 

  1. Aşağıdaki SQL cümleciğini text editörünüze kopyalayın

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:query>
    SELECT
    1 as Tag,
    null as Parent,
    Category.CategoryName as Category!1!Name,
    null as Product!2!Name,
    null as ProductDetail!3!Supplier,
    null as ProductDetail!3!Price,
    null as ProductDetail!3!NumberSold
    FROM
    Categories Category

    UNION ALL
    SELECT
    2,
    1,
    Category.CategoryName,
    Product.ProductName,
    null,
    null,
    null
    FROM
    Categories Category
    INNER JOIN Products Product
    on Category.CategoryID = Product.CategoryID

    UNION ALL
    SELECT
    3,
    2,
    Category.CategoryName,
    Product.ProductName,
    null,
    Product.UnitPrice,
    null
    FROM
    Categories Category
    INNER JOIN Products Product
    on Category.CategoryID = Product.CategoryID
    INNER JOIN Suppliers s
    On Product.SupplierID = s.SupplierID

    UNION ALL
    SELECT
    3,
    2,
    Category.CategoryName,
    Product.ProductName,
    s.CompanyName,
    null,
    null
    FROM
    Categories Category
    INNER JOIN Products Product
    on Category.CategoryID = Product.CategoryID
    INNER JOIN Suppliers s
    on Product.SupplierID = s.SupplierID

    UNION ALL
    SELECT
    3,
    2,
    Category.CategoryName,
    Product.ProductName,
    null,
    null,
    sum(Quantity)
    FROM
    Categories Category
    INNER JOIN Products Product
    on Category.CategoryID = Product.CategoryID
    INNER JOIN Suppliers s
    on Product.SupplierID = s.SupplierID
    LEFT JOIN [Order Details] od
    ON Product.ProductID = od.ProductID
    group by Category.CategoryName,Product.ProductName,s.CompanyName
    ORDER BY
    Category!1!Name,
    Product!2!Name,
    ProductDetail!3!Supplier,
    ProductDetail!3!Price,
    ProductDetail!3!NumberSold
    FOR XML EXPLICIT
    </sql:query>
    </ROOT>
     
  2. C:\Inetpub\wwwroot\xmltest dizini altına Catalog.xml adını vererek kaydedin.
  3. Internet Explorera açıp adres çubuğuna aşağıdaki adresi yazıp ENTER tuşuna basınız.
    http://localhost/xmltest/templates/catalog.xml

Sonuç hiyerarşiktir; bu sonuç diyerlerine göre farklıdır. Önceki sonuçlarda bilgiler elementin üyeleri(attribute) olarak görünmekteydi. Burada ise her bir parçanın bilgisi XML element olarak görünmektedir.
 

- <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
- <Category Name="Beverages">
- <Product Name="Chai">
  <ProductDetail NumberSold="828" />
  <ProductDetail Price="18" />
  <ProductDetail Supplier="Exotic Liquids" />
  </Product>
- <Product Name="Lakkalikööri">
  <ProductDetail NumberSold="981" />
  <ProductDetail Price="18" />
  <ProductDetail Supplier="Karkki Oy" />
  </Product>
</Category>
- <Category Name="Condiments">
- <Product Name="Aniseed Syrup">
  <ProductDetail NumberSold="328" />
  <ProductDetail Price="10" />
  <ProductDetail Supplier="Exotic Liquids" />
  </Product>
</Category>
  </ROOT>

Evet şimdi burya kadar bir datayı Internet Explorer ve IIS yardımıyla nasıl dış dünyaya sunarız ve XML dokümanımızı nasıl formatlarız bunları gördük. Veriyi XML olarak Internet Explorerdan görmek son kullanıcı için pek anlamlı değildir. Peki bunu güzel bir kullanıcı arayüzü ile son kullanıcıya gösterebilirmiyiz. Buna cevap olarak kesinlikle evet dediğinizi şimdiden duyar gibiyim. Evet XMLe kullanıcı arayüzü uygulayabiliriz. Bunu XSL yardımıyla yapacağız. Aslında XSLin derya deniz bir konu olduğunu hepimiz biliyoruz. Bu makalede XSLden pek bahsetmiyorum konu ile ilgili makaleleri sitemizin XML Web service bölümünde bulabilirsiniz.

XSL Style Sheets(Biçim Sayfaları)

Bunu bir örnekle açıklayalım. Northwind databaseindeki Employee tablosundaki bazı bilgileri iç kullanıcılara sunmak istiyorsunuz. Burada şu soru akla gelebilir; Employee tablosundaki bilgileri neden XML kullanarak yayınlayayım güzel bir ara yüz ile ASP yardımıylada bunu yapabilirim diyebilrsiniz ve bunu sormada da çok haklısınız. Peki sizin insan kaynakları programınız ve muhasebe programınız entegre çalışmıyor ve iki firma XML ile data alışverişi konusunda hem fikir oldular ve çalışan bilgilerini  XML ile anlaşıyorlar. Sizden de bu bilgileri kullanıcıların hizmetine sunmanızı istediler. Hazır elinizde böyle bir kaynak varken neden birde asp ile buğuşanız ki diye bende sorarım. Neyse uzun lafı keselim de örneğe gecelim...

Yukarda verdiğimiz bilgiler doğrultusunda aşağıda verilen kodu C:\Inetpub\wwwroot\xmltest altına EmpList.xml adında kaydedin.

<?xml version =1.0 encoding=UTF-8?>
<root xmlns:sql=urn:schemas-microsoft-com:xml-sql>
<sql:query>
SELECT FirstName, LastName, Extension Extension FROM Employees FOR XML AUTO
</sql:query>
</root>

Çalışıp çalışmadığını kontrol etmek için Internet Explorerı çalıştırıp adres çubuğuna http://localhost/xmltest/templates/emplist.xml yazıp ENTER tuşuna basın. Ben denedim çalışmadı :) çünkü dosyayı kaydetmeyi unutmuşum. Dosyayı kaydettikten sonra çalıştı.

Evet şimdide XSLimizi oluşturup kaydedelim. XSL html doküman oluşturmak için kullanılıyor burada.
 

<?xml version=1.0 encoding=UTF-8?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match = *>
<xsl:apply-templates />
</xsl:template>
<xsl:template match = Employees>
<TR>
<TD><xsl:value-of select = @FirstName /></TD>
<TD><B><xsl:value-of select = @LastName /></B></TD>
<TD><xsl:value-of select = @Extension /></TD>
</TR>
</xsl:template>
<xsl:template match = />
<HTML>
<HEAD>
<STYLE>th { background-color: #CCCCCC }</STYLE>
</HEAD>
<BODY>
<TABLE border=1 style=width:300;>
<TR><TH colspan=3>Employees</TH></TR>
<TR><TH >First name</TH><TH>Last name</TH><TH>Extension</TH></TR>
<xsl:apply-templates select = root />
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>
 

 

Evet bu XSLlide C:\Inetpub\wwwroot\xmltest dizinine empInternal.xsl adını verip kaydedin. Şimdide herşeyi doğru yapmışmıyız bir kontrol edelim bakalım. Test için Internet Explorerı çalıştırın ve adres çubuğuna http://localhost/xmltest/templates/emplist.xml?xsl=empInternal.xsl adresini yazıp ENTER tuşuna basın. Çıkan sonuç daha anlaşılır ve hoş değil mi?

XSL üzerinde çeşitli değişiklikler yaparak çıkan sonuçu değiştirebilirsiniz. Örneğin sadece çalışanların ad ve soyadlarının görünmesini istiyorsanız XSL içeridindeki <TD><xsl:value-of select = @Extension /></TD> satırını silip deneye bilirsiniz. Renklerle oynayıp istediğiniz rekleri verebilrisiniz. XSL üzerinde değişiklik yaparken dikkat etmeniz gereken nokta XSLin iyi formatlanmış olması gerekir.
 

Şimdilik yazacaklarım bu kadar umarım faydalı olmuştur. Eğer bir sorunuz yada takıldığınız nokta varsa hiç çekinmeden aşağıdaki e-mail adresinden bana ulaşabilrsiniz. Vaktim olduğu sürece size cevap döneceğimdir.

İşlerinizde başarılar bol kazançlı ve mutluluk dolu yarınlar sizlerle olsun

Ahmet ÖZTÜRK
aozturk@sfs.com.tr