XML

XML (Extensible Markup Language), veriyi etiketlerle tanımlayan, metin tabanlı ve hiyerarşik bir işaretleme dilidir. Excel XML dosyalarını içeri aktarabilir, bir şemaya eşlenen hücre verisini XML olarak dışarı aktarabilir ve metin olarak tutulan XML'i formüllerle okuyabilir. Farklı sistemler arasında veri alışverişinde yaygın olarak kullanılır; .xlsx gibi modern Excel dosya biçimlerinin içi de XML parçalarından oluşur.

XML'in yapısı ve Excel'deki yeri​

Bir XML belgesi tek bir kök öğe içinde iç içe geçmiş öğelerden (element) oluşur. Her öğenin bir açılış ve bir kapanış etiketi vardır; öğelerin ayrıca nitelikleri (attribute) olabilir. Örneğin bir ürün kaydı ad, fiyat ve stok öğelerinden oluşabilir; aynı yapı tekrar ettiğinde Excel bu tekrarları tablo satırları gibi görür.

XML'in Excel'de kullanıldığı başlıca yerler şunlardır:

• Dosya biçimi: .xlsx, .xlsm gibi Open XML biçimleri aslında XML dosyalarını içeren sıkıştırılmış paketlerdir.
• Veri alma: Dış sistemlerden, web hizmetlerinden ya da muhasebe ve ERP programlarından gelen .xml dosyaları Excel'e aktarılabilir.
• Veri verme: Bir XML şemasına eşlenmiş hücre aralığı .xml dosyası olarak dışarı aktarılabilir.
• Formüller: Hücrede metin olarak duran XML, FILTERXML işleviyle okunabilir.

Önemli komutlar şu yerlerde bulunur: Veri > Veri Al ve Dönüştür > Veri Al > Dosyadan > XML'den (Data > Get & Transform Data > Get Data > From File > From XML) ve Geliştirici > XML (Developer > XML) grubu. Geliştirici sekmesi görünmüyorsa Dosya > Seçenekler > Şeridi Özelleştir (File > Options > Customize Ribbon) bölümünden Geliştirici kutusunu işaretleyin. Dosya açma penceresi için Ctrl+O kısayolunu kullanabilirsiniz.

XML dosyasını içeri aktarma​

Bir XML dosyasını Excel'e almanın en güncel yolu Power Query'dir. Adımlar şöyledir:

• 1. Komutu seçin: Veri > Veri Al ve Dönüştür > Veri Al > Dosyadan > XML'den yolunu izleyin.
• 2. Dosyayı seçin: .xml dosyasını bulup İçeri Aktar (Import) düğmesine tıklayın.
• 3. Tabloyu seçin: Gezgin (Navigator) penceresinde dosyanın içindeki tabloları görürsünüz. Gerekeni seçin.
• 4. Yükleyin: Veriyi olduğu gibi almak için Yükle (Load), düzenlemek için Veriyi Dönüştür (Transform Data) komutunu kullanın.
• 5. Alt öğeleri genişletin: Power Query Düzenleyicisi'nde Table ya da Record görünen sütunların başlığındaki genişletme okuyla iç içe öğeleri sütunlara açın.

Dosya > Aç ile bir .xml dosyasını doğrudan açtığınızda Excel, dosyayı XML tablosu olarak, salt okunur çalışma kitabı olarak ya da XML Kaynak bölmesiyle açma seçenekleri sunar. Kaynak verisi değiştikçe yenilemek istiyorsanız Power Query yöntemi daha uygundur; sorguyu Veri > Tümünü Yenile (Data > Refresh All) ile güncelleyebilirsiniz.

XML eşleme ve dışarı aktarma​

Hücre verisini XML olarak dışarı vermek için önce bir XML şeması (.xsd) ile eşleme yapılır. Geliştirici > Kaynak (Developer > Source) komutu XML Kaynak görev bölmesini açar. Buradaki XML Eşlemeleri (XML Maps) düğmesiyle şemayı çalışma kitabına ekler, ardından şemadaki öğeleri sürükleyip hücrelere bırakırsınız. Tekrarlanan öğeler bir XML listesi (tablo) olarak yerleşir.

Eşleme tamamlandıktan sonra kullanılan komutlar Geliştirici sekmesindeki XML grubundadır:

• İçeri Aktar (Import): Bir XML dosyasını eşlenmiş hücrelere yükler.
• Dışarı Aktar (Export): Eşlenmiş hücrelerdeki veriyi .xml dosyası olarak kaydeder.
• Verileri Yenile (Refresh Data): Bağlı XML kaynağından veriyi tazeler.
• Eşleme Özellikleri (Map Properties): Eşlemenin doğrulama ve üzerine yazma davranışını belirler.

Eşleme yoksa Dışarı Aktar komutu kullanılamaz. Dışarı aktarırken hücre değerlerinin şemadaki veri türleriyle uyumlu olması gerekir; uyumsuz veri varsa Excel dışarı aktarmayı uyarıyla durdurabilir.

Formüllerle XML okuma ve üretme​

Hücrede metin olarak duran bir XML'den değer almak için FILTERXML işlevi kullanılır. İlk bağımsız değişken XML metnini, ikincisi okunacak düğümü gösteren XPath ifadesini alır:
XPath içinde "//ad" belgenin herhangi bir yerindeki ad öğesini, "//kisi[2]/ad" ise ikinci kisi öğesinin adını verir. Ancak count() gibi bir XPath işlevi, aşağıdaki ikinci örnekte #DEĞER! hatası döndürmüştür; bu nedenle kayıt sayısını almak için count() işlevine güvenmeyin.

Tersi de mümkündür: tablo satırlarını & birleştirme işleciyle etiketlerin arasına yerleştirerek XML metni üretebilirsiniz. Üretilen metin içindeki & gibi özel karakterleri & olarak değiştirmeyi unutmayın. Aşağıdaki örnekler bu iki yaklaşımı gösterir.

1. FILTERXML ile XML metninden değer okuma​

A2 hücresine tek bir ürün kaydını anlatan XML metni yazılır. C sütununda her alanın XPath ifadesi, D sütununda ise FILTERXML formülü bulunur. Formülü D2 hücresine yazıp D4 hücresine kadar aşağı kopyalayın; XML metni mutlak başvuruyla ($A$2) sabitlenmiştir.
D2 hücresi XML içindeki ad öğesini (Defter), D3 fiyat öğesini (125), D4 ise stok öğesini (40) getirir. Fiyat ve stok değerleri metin değil sayı olarak döner, bu yüzden doğrudan hesaplamalarda kullanılabilir.

2. Tekrarlanan öğeleri sıra numarasıyla okuma​

A2 hücresinde üç kisi öğesi içeren bir XML metni vardır. B sütunundaki sıra numarası XPath ifadesine birleştirilerek her kişinin adı ve şehri ayrı ayrı okunur. E2 hücresindeki formül ise kayıt sayısını XPath count() işleviyle bulmayı dener, ancak #DEĞER! hatası döndürür. Formülleri 2. satıra yazıp 4. satıra kadar kopyalayın.
C2 ve D2 hücreleri birinci kişinin adını (Ayşe) ve şehrini (Ankara) verir; aşağı kopyalandığında sıra numarası değiştiği için sonraki kişiler gelir. E2 hücresindeki count() formülü ise kişi sayısını getirmez, #DEĞER! hatası döndürür. Bu nedenle kayıt sayısını bu formülle alamazsınız. Sıra numarasını değiştirerek istediğiniz kaydı okuyabilirsiniz.

3. Tablodan XML metni üretme​

A:C sütunlarında ürün tablosu bulunur. D sütunundaki formül her satırı bir urun öğesine dönüştürür; ürün adındaki & karakteri SUBSTITUTE ile & biçimine çevrilir. E2 hücresi tüm satırları TEXTJOIN ile birleştirip katalog kök öğesinin içine alır. D2 formülünü D6 hücresine kadar kopyalayın.
D2 hücresi ilk ürünün XML satırını (<urun><ad>Defter &amp; Kalem Seti</ad><fiyat>150</fiyat><stok>25</stok></urun>) üretir; ürün adındaki & karakteri &amp; olarak yazıldığı için XML geçerli kalır. E2 hücresi tüm satırları tek kök öğe altında birleştirir (<katalog><urun><ad>Defter &amp; Kalem Seti</ad><fiyat>150</fiyat><stok>25</stok></urun><urun><ad>Ataç</ad><fiyat>20</fiyat><stok>300</stok></urun><urun><ad>Dosya Klasörü</ad><fiyat>45</fiyat><stok>80</stok></urun><urun><ad>Zımba</ad><fiyat>85</fiyat><stok>60</stok></urun><urun><ad>Bant</ad><fiyat>30</fiyat><stok>120</stok></urun></katalog>). Bu metni bir .xml dosyasına yapıştırıp kaydederek başka sistemlere verebilir ya da FILTERXML ile yeniden okuyabilirsiniz. Fiyatlarda ondalık ayırıcı sorun çıkarmasın diye örnekte tam sayılar kullanılmıştır.

Kullanırken dikkat edin​

• XML geçerli (well-formed) olmalıdır: Her açılan etiket kapatılmalı, tek bir kök öğe bulunmalı ve etiket adlarında büyük/küçük harfe dikkat edilmelidir. Hatalı XML'i içeri aktarma sırasında Excel hata verir, FILTERXML ise #DEĞER! hatası döndürür. Metin içindeki &, < ve > karakterleri &amp;, &lt; ve &gt; olarak yazılmalıdır.
• FILTERXML sürüm ve sınır uyarısı: FILTERXML işlevi Excel 2013 ve sonrasındaki Windows sürümlerinde bulunur; Mac ve web sürümlerinde kullanılamayabilir. Bir hücreye en fazla 32.767 karakter sığdığı için büyük XML belgeleri hücre metni olarak tutulamaz; bu durumda Power Query ile içeri aktarmayı tercih edin. XPath ifadeleri büyük/küçük harfe duyarlıdır.
• Dışarı aktarma için eşleme gerekir: Geliştirici sekmesindeki Dışarı Aktar komutu yalnızca bir XML eşlemesi bulunan çalışma kitaplarında kullanılabilir. Şemadaki bazı karmaşık yapılar Excel'in XML eşlemesinde desteklenmez; böyle bir şema eklendiğinde Excel uyarı verir ve öğeleri eşlemenize izin vermeyebilir. Basit, tablo benzeri yapılarda sorun yaşamazsınız.
• Ondalık ve tarih biçimleri: XML'de sayılar noktalı ondalık, tarihler ise genellikle YYYY-AA-GG biçiminde yazılır. Türkçe bölgesel ayarlarda içeri aktarılan değerler bazen metin olarak kalabilir; böyle durumlarda sütun veri türünü Power Query'de değiştirin ya da sayıya dönüştürün.
• Geliştirici sekmesini açmayı unutmayın: XML grubundaki Kaynak, İçeri Aktar ve Dışarı Aktar komutları varsayılan olarak gizli olan Geliştirici sekmesindedir. Sekmeyi Dosya > Seçenekler > Şeridi Özelleştir bölümünden etkinleştirin.
• İlgili konular:
  • Veri Al ve Dönüştür (Power Query): XML, JSON ve diğer kaynaklardan veri alma ve yenileme
  • Geliştirici sekmesi: XML Kaynak bölmesi, XML Eşlemeleri, İçeri/Dışarı Aktar
  • XMLFİLTRELE: Metin olarak duran XML'den XPath ile değer okur
  • WEBHİZMETİ: Bir web adresinden XML yanıtı alır; XMLFİLTRELE ile birlikte kullanılır
  • METİNBİRLEŞTİR: Satırlardan birleşik XML metni üretmek için
  • YERİNEKOY: XML'de özel karakterleri değiştirmek için
  • CSV ve JSON: Veri alışverişinde kullanılan diğer biçimler

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.