How to generate master detail xml file in sql 2005 and 2000

Question

How to generate master detail xml file in sql 2005 and 2000

aysegul

Right there with Babe

Points: 778
More actions
January 12, 2010 at 3:01 pm

#96793

Hi All,
I wrote a query as below.
select KB.KartelaNo as KBBarkod, KB.HambezStokAnmaKodu as KBHambezStokAnmaKodu,
SH.StokAnmaAdi as KBHambezStokAnmaAdi,
KB.ZeminRenkKodu, KB.En, KB.EnTolerans, KB.GramajGrm2, KB.GramajTolerans, KB.Konstruksiyon, KB.Aciklama as 'Kumas/Aciklama'
, KD.DosyaID as 'Kumas/KDBarkod', KD.DesenNo as 'Kumas/DesenNo', KD.VaryantNo as 'Kumas/VaryantNo', KD.HambezStokAnmaKodu as 'Kumas/KDHambezStokAnmaKodu'
, 'Kumas/KDHambezStokAnmaAdi' = (Select TOP 1 StokAnmaAdi From StokHambezler SHM(NoLock) Where StokAnmaKodu = KD.HambezStokAnmaKodu)
, 0.0 as 'Kumas/Fiyat', NULL as 'Kumas/ParaBirimi', NULL as 'Kumas/OlcuBirimi'
from Kartela KB(NoLock)
left outer join KartelaDesenVaryant KD(NoLock) on KD.KartelaID = KB.DosyaID
left outer join StokHambezler SH(NoLock) on KB.HambezStokAnmaKodu = SH.StokAnmaKodu
where KB.KartelaNo in
(Select KartelaNo
From Kartela K (NoLock))
order by KB.KartelaNo, KD.DosyaID
FOR XML PATH('Kartela'), ROOT('Kartelalar'), type ,ELEMENTS XSINIL
I want to configure the query like this.
<Kartelalar>
<Kartela>
<Kumas>
</Kumas>
<Kumas>
</Kumas>
<Kumas>
</Kumas>
</Kartela>
</Kartelalar>
How can I do this ?
Kind regards
Ayşegül

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Creating the XML in that format should not be very difficult, but I'll need some more information from you to continue. Can you post the table DDL for the tables involved as well as sample data in a readily consumable format?

See the article in my signature on forum etiquette for instructions on how to help us help you.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

aysegul Right there with Babe Points: 778 More actions · Answer 2

Hi,

I modified my query as below

select

KB.KartelaNo as KBBarkod,

KB.HambezStokAnmaKodu as KBHambezStokAnmaKodu,

SH.StokAnmaAdi as KBHambezStokAnmaAdi,

KB.ZeminRenkKodu,

KB.En,

KB.EnTolerans,

KB.GramajGrm2,

KB.GramajTolerans,

KB.Konstruksiyon,

(select

K.Aciklama as 'Aciklama'

,KD.DosyaID as 'KDBarkod',

KD.DesenNo as 'DesenNo',

KD.VaryantNo as 'VaryantNo',

KD.HambezStokAnmaKodu as 'KDHambezStokAnmaKodu'

, 'KDHambezStokAnmaAdi' = (Select TOP 1 StokAnmaAdi From StokHambezler SHM(NoLock) Where StokAnmaKodu = KD.HambezStokAnmaKodu)

, 0.0 as 'Fiyat', NULL as 'ParaBirimi', NULL as 'OlcuBirimi'

from KartelaDesenVaryant KD(NoLock) inner join Kartela K(NoLock)

on K.DosyaID=KD.KartelaID

where K.DosyaID = KB.DosyaID

FOR XML PATH ('Kumas'), TYPE,ELEMENTS XSINIL)

from Kartela KB(NoLock)

left outer join StokHambezler SH(NoLock) on KB.HambezStokAnmaKodu = SH.StokAnmaKodu

where KB.KartelaNo in

(Select KartelaNo

From Kartela K (NoLock))

order by KB.KartelaNo

FOR XML PATH('Kartela'), ROOT('Kartelalar'), type ,ELEMENTS XSINIL

I got my result.

Thanks for your help.

LutzM SSC Guru Points: 107049 More actions · Answer 3

I'll second Johns question regarding more details.

Please provide table definition together with sample data and expected result.

Some of us like to provide tested solutions. So you'll increase the number of volunteers by providing ready to use data.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]