SQLTeam.com | Weblogs | Forums

Importing xml file

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

Hello

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

hi

hope this link helps

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

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

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

thx

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

hi

hope this link helps ..

--->>>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

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

and put them in before and after the xml

<root>
  <parent>
       <child></child>
</parent>
</root>
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:

:frowning:

maybe you can post an image of it

Maybe this will help?

image

1 Like

thx

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);

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

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);