how to make a script sql with results like this (X

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

how to make a script sql with results like this (X

Author

Topic

nizw_r
Starting Member

2 Posts

Posted - 2014-12-19 : 09:37:39

how to make a script sql with results like this (XML)?

<ns1:inputRealisasiAPBDperPeriode'>
<realisasiApbd>
<kodeSatker>991947</kodeSatker>
<tahunAnggaran>2014</tahunAnggaran>
<kegiatan>
<kodeUrusanProgram>1.01</kodeUrusanProgram>
<namaUrusanProgram>Pendidikan</namaUrusanProgram>
<koderekening>
<kodeAkunRincian>01</kodeAkunRincian>
<namaAkunRincian>Retribusi</namaAkunRincian>
</koderekening>
<koderekening>
<kodeAkunRincian>02</kodeAkunRincian>
<namaAkunRincian>Gaji Pokok</namaAkunRincian>
</koderekening>
</kegiatan>
<kegiatan>
<kodeUrusanProgram>1.02</kodeUrusanProgram>
<namaUrusanProgram>Perpustakaan</namaUrusanProgram>
<koderekening>
<kodeAkunRincian>01</kodeAkunRincian>
<namaAkunRincian>Retribusi</namaAkunRincian>
</koderekening>
</kegiatan>
</realisasiapbd>
</ns1:inputRealisasiAPBDperPeriode'>

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-19 : 11:16:25

look up the FOR XML clause of the SELECT statement:

http://msdn.microsoft.com/en-ca/library/ms178107.aspx

nizw_r
Starting Member

2 Posts

Posted - 2014-12-20 : 06:41:06

I make a script like this :

SELECT KODE_GIAT ,
( select * from tm_rincian_obyek
where (tm_rincian_obyek.kode_rekening =tr_apbd.kode_rekening)
FOR XML AUTO, TYPE, ELEMENTS) AS kode_rekening
FROM
tr_apbd
group by kode_giat,kode_rekening
ORDER BY KODE_GIAT
FOR XML auto, ELEMENTS

Result :
<tr_apbd>
<KODE_GIAT>1.01.01.00.00</KODE_GIAT>
<kode_rekening>
<tm_rincian_obyek>
<KODE_AKUN>5</KODE_AKUN>
<KD_KEL_REK>1</KD_KEL_REK>
<KD_JENIS_REK>1</KD_JENIS_REK>
<KODE_OBYEK>01</KODE_OBYEK>
<KODE_RINCIAN>01</KODE_RINCIAN>
<KODE_REKENING>5.1.1.01.01</KODE_REKENING>
<RINCIAN_OBYEK>Gaji Pokok PNS/Uang Representasi</RINCIAN_OBYEK>
</tm_rincian_obyek>
</kode_rekening>
</tr_apbd>
<tr_apbd>
<KODE_GIAT>1.01.01.00.00</KODE_GIAT>
<kode_rekening>
<tm_rincian_obyek>
<KODE_AKUN>5</KODE_AKUN>
<KD_KEL_REK>1</KD_KEL_REK>
<KD_JENIS_REK>1</KD_JENIS_REK>
<KODE_OBYEK>01</KODE_OBYEK>
<KODE_RINCIAN>02</KODE_RINCIAN>
<KODE_REKENING>5.1.1.01.02</KODE_REKENING>
<RINCIAN_OBYEK>Tunjangan Keluarga</RINCIAN_OBYEK>
</tm_rincian_obyek>
</kode_rekening>
</tr_apbd>
<tr_apbd>
<KODE_GIAT>1.01.01.00.00</KODE_GIAT>
<kode_rekening>
<tm_rincian_obyek>
<KODE_AKUN>5</KODE_AKUN>
<KD_KEL_REK>1</KD_KEL_REK>
<KD_JENIS_REK>1</KD_JENIS_REK>
<KODE_OBYEK>01</KODE_OBYEK>
<KODE_RINCIAN>03</KODE_RINCIAN>
<KODE_REKENING>5.1.1.01.03</KODE_REKENING>
<RINCIAN_OBYEK>Tunjangan Jabatan</RINCIAN_OBYEK>
</tm_rincian_obyek>
</kode_rekening>
</tr_apbd>

but,
i want to get result like this :
<tr_apbd>
<KODE_GIAT>1.01.01.00.00</KODE_GIAT>
<kode_rekening>
<tm_rincian_obyek>
<KODE_AKUN>5</KODE_AKUN>
<KD_KEL_REK>1</KD_KEL_REK>
<KD_JENIS_REK>1</KD_JENIS_REK>
<KODE_OBYEK>01</KODE_OBYEK>
<KODE_RINCIAN>01</KODE_RINCIAN>
<KODE_REKENING>5.1.1.01.01</KODE_REKENING>
<RINCIAN_OBYEK>Gaji Pokok PNS/Uang Representasi</RINCIAN_OBYEK>
</tm_rincian_obyek>
<tm_rincian_obyek>
<KODE_AKUN>5</KODE_AKUN>
<KD_KEL_REK>1</KD_KEL_REK>
<KD_JENIS_REK>1</KD_JENIS_REK>
<KODE_OBYEK>01</KODE_OBYEK>
<KODE_RINCIAN>02</KODE_RINCIAN>
<KODE_REKENING>5.1.1.01.02</KODE_REKENING>
<RINCIAN_OBYEK>Tunjangan Keluarga</RINCIAN_OBYEK>
</tm_rincian_obyek>
<tm_rincian_obyek>
<KODE_AKUN>5</KODE_AKUN>
<KD_KEL_REK>1</KD_KEL_REK>
<KD_JENIS_REK>1</KD_JENIS_REK>
<KODE_OBYEK>01</KODE_OBYEK>
<KODE_RINCIAN>03</KODE_RINCIAN>
<KODE_REKENING>5.1.1.01.03</KODE_REKENING>
<RINCIAN_OBYEK>Tunjangan Jabatan</RINCIAN_OBYEK>
</tm_rincian_obyek>
</kode_rekening>
</tr_apbd>

please help..........
how to make produce the script as above, please help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources