Importing an xml file

Other SQL Server Topics
#1

Hi,
I have data as below
http://svc.efaktur.pajak.go.id/validasi/faktur/316893429418000/0181792181052/3031300D060960864801650304020105000420D5055629622EF483463A39941E435BE258F3A32AC6606EDA3432830507BAEB31

it is from scan barcode device, and it's resulting an xml
data and I need to store it into my sql table,
does anyone can tell me how?

thx,

Joe

#2

Check out this article: https://www.red-gate.com/simple-talk/sql/database-administration/manipulating-xml-data-in-sql-server/

#3

couple of ways to do it.

DECLARE @xmlProducts xml;
SET @xmlProducts =
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<resValidateFakturPm>
  <kdJenisTransaksi>01</kdJenisTransaksi>
  <fgPengganti>0</fgPengganti>
  <nomorFaktur>0181792181052</nomorFaktur>
  <tanggalFaktur>07/12/2017</tanggalFaktur>
  <npwpPenjual>316893429418000</npwpPenjual>
  <namaPenjual>PT TITON MAJU SENTOSA</namaPenjual>
  <alamatPenjual>JL. RAYA PRANCIS PERGUDANGAN MUTIARA KOSAMBI I BLOK A6 NO 17 , KAB. TANGERANG</alamatPenjual>
  <npwpLawanTransaksi>016730913641000</npwpLawanTransaksi>
  <namaLawanTransaksi>PT. MITRACITRA MANDIRIOFFSET</namaLawanTransaksi>
  <alamatLawanTransaksi>JL. RAYA SURABAYA MOJOKERTO KM 19 Blok - No.- RT:000 RW:000 Kel.BRINGIN BENDO Kec.BRINGIN BENDO TAMAN Kota/Kab.SIDOARJO JAWA TIMUR 61257</alamatLawanTransaksi>
  <jumlahDpp>5256000</jumlahDpp>
  <jumlahPpn>525600</jumlahPpn>
  <jumlahPpnBm>0</jumlahPpnBm>
  <statusApproval>Faktur Valid, Sudah Diapprove oleh DJP</statusApproval>
  <statusFaktur>Faktur Pajak Normal</statusFaktur>
  <referensi>TMJS/12/2017/FKP/541</referensi>
  <detailTransaksi>
    <nama>PLASTIK WRAP</nama>
    <hargaSatuan>73000</hargaSatuan>
    <jumlahBarang>72</jumlahBarang>
    <hargaTotal>5256000</hargaTotal>
    <diskon>0</diskon>
    <dpp>5256000</dpp>
    <ppn>525600</ppn>
    <tarifPpnbm>0</tarifPpnbm>
    <ppnbm>0</ppnbm>
  </detailTransaksi>
</resValidateFakturPm>'
select
    Tab.Col.value('kdJenisTransaksi[1]', 'varchar(10)') as kdJenisTransaksi,
    Tab1.Col1.value('nama[1]', 'varchar(10)') as nama,
    Tab1.Col1.value('hargaSatuan[1]', 'varchar(10)') as hargaSatuan,
    Tab1.Col1.value('jumlahBarang[1]', 'varchar(10)') as jumlahBarang
from @xmlProducts.nodes('/resValidateFakturPm') as Tab(Col)
cross apply Tab.Col.nodes('detailTransaksi') as Tab1(Col1)

select Tab.Col.value('nama[1]', 'varchar(10)') as nama,
       Tab.Col.value('../kdJenisTransaksi[1]', 'varchar(10)') as kdJenisTransaksi	 
from @xmlProducts.nodes('/resValidateFakturPm/detailTransaksi') Tab(Col)
#4

thanks a lot..let me try