Importing xml file

1

Hi,
Does anyone have any experiences to share about importing xml file ? I have xml file containing parent child information, still don't understand to store it into my tables. And the xml file is taken from an url ...

thx,

Joe

2

Hello

It would help if you posted the xml. Use 3 code ticks to post xml `

3

hi

hope this link helps

please google search LOTS LOTS LOTS LOTS :slight_smile: of links

4

This is the xml file. It is from qr scanning process. and I still dont understand the process from qr scan then save it into the table..great full you can help. This is the file :

01 0 01917XXX 17/10/2017 010700XXX PT.ANGINXXX JL.PAJAJARAN NO.XX RT.XXI RW.XX,JUWUNG , JOMBANG 0167000XXX PT. MENDOYO XXXX JL. RAYA XXXXXX 250000 250000 0 Faktur Valid-DJP Normal NOMOR INVOICE: 9XXX57X TAPIOKA 10 KG 42000 10 420000 0 420000 42000 0 0 GULA 12 KG 15000 10 150000 0 150000 15000 0 0
5

how about for the xml that has parent child data? do you have the link also?

thx

6

Please repost with three code ticks for posting xml. What you posted is not xml

Use three of the following tick mark -> ` <-before and after xml

7

hi

hope this link helps ..

8

--->>>before XML -->>>
'
01
0
01917XXX
17/10/2017
010700XXX
PT.ANGINXXX
JL.PAJAJARAN NO.XX RT.XXI RW.XX,JUWUNG , JOMBANG
0167000XXX
PT. MENDOYO LANGGENG
JL. RAYA XXXXXX
250000
250000
0
Faktur Valid-DJP
Normal
NOMOR INVOICE: 9XXX57X

TAPIOKA 10 KG
42000
10
420000
0
420000
42000
0
0


GULA 12 KG
15000
10
150000
0
150000
15000
0
0

'

<<<----After XML

9

:laughing:
copy 3 of these ticks
``` `````````````````````````````````

and put them in before and after the xml

<root>
  <parent>
       <child></child>
</parent>
</root>
10
01 0 01917XXX 17/10/2017 010700XXX PT.ANGINXXX JL.PAJAJARAN NO.XX RT.XXI RW.XX,JUWUNG , JOMBANG 0167000XXX PT. MENDOYO LANGGENG JL. RAYA XXXXXX 250000 250000 0 Faktur Valid-DJP Normal NOMOR INVOICE: 9XXX57X TAPIOKA 10 KG 42000 10 420000 0 420000 42000 0 0 GULA 12 KG 15000 10 150000 0 150000 15000 0 0 '

hope that correct...peace... :grin: :grin:

11

:frowning:

maybe you can post an image of it

12

Maybe this will help?

image

1 Like
13

efaktr
efaktr663×589 75 KB

thx

14

and now please tell us how do you want it saved in sql table? which parts of the xml go to which column etc

;with src
as
(
	SELECT CONVERT(XML, MY_XML) AS xmlski
	  FROM OPENROWSET(BULK 'C:\SQLImports\data.xml', SINGLE_BLOB) AS T(MY_XML)
)
--insert into targetTable
select parent.query('kdJenisTransaksi').value('.', 'int') as kdJenisTransaksi,
       detail.query('nama').value('.', 'VARCHAR(20)') as nama       
  from src 
 CROSS APPLY xmlski.nodes('resValidateFakturPm') AS a(parent)
 CROSS APPLY xmlski.nodes('detailTransaksi') AS b(detail);
15

Hi,
There are 2 parts of the file I need to save it into tables. first table contain main table (parent) and second will be the child (there are 2 records). It shown on 2 pictures attached...
Thanks A lot.... childs

parent
parent732×365 59.3 KB

16

please post using the following 3 tick marks before and after xml test and not an image

3 of these before the xml and after the xml text
image

;with src
as
(
	SELECT CONVERT(XML, MY_XML) AS xmlski
	  FROM OPENROWSET(BULK 'C:\SQLImports\data.xml', SINGLE_BLOB) AS T(MY_XML)
)
--insert into targetTable
select parent.value('kdJenisTransaksi[1]', 'VARCHAR(100)') AS kdJenisTransaksi,
       child.value('nama[1]', 'VARCHAR(100)') AS nama    
  from src 
 CROSS APPLY xmlski.nodes('PARENT/resValidateFakturPm') AS a(parent)
 CROSS APPLY xmlski.nodes('CHILD/detailTransaksi') AS b(child);