Retrieve data from an XML file

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

General SQL Server Forums

New to SQL Server Programming

Retrieve data from an XML file

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-12 : 19:19:04

Hi,
I have a problem and I don't know really where to start. I am trying to retrieve data from an XML file and import it into a table in my database.

This is the data in my XML file:

<FAKTURA>
<FÖRETAG>
<NAMN>Microsaft</NAMN>
<ORGNR>733423-2340</ORGNR>
<ADRESS>Smuggelhamnen 1</ADRESS>
<POSTNUMMER>152 66</POSTNUMMER>
<ORT>Stockholm</ORT>
<TELEFON>08-555 66 56</TELEFON>
<POSTGIRO>8473940-8</POSTGIRO>
</FÖRETAG>
<KÖPARE>
<FÖRETAGSNAMN>Norne</FÖRETAGSNAMN>
<BESTÄLLARE>Kurt Hansson</BESTÄLLARE>
</KÖPARE>
<FAKTURA_DETALJER>
<FAKTURA_NR>1001</FAKTURA_NR>
<DATUM>2006-04-05</DATUM>
<TOTAL_SUMMA>2242</TOTAL_SUMMA>
<TOTAL_MOMS>448,40</TOTAL_MOMS>
</FAKTURA_DETALJER>
<FAKTURA_RADER>
<RAD>
<ARTIKELNAMN>Absolut Citron</ARTIKELNAMN>
<ARTIKELNUMMER>98</ARTIKELNUMMER>
<ANTAL>3</ANTAL>
<PRIS_ST>245</PRIS_ST>
</RAD>
<RAD>
<ARTIKELNAMN>Calvados Camut</ARTIKELNAMN>
<ARTIKELNUMMER>279</ARTIKELNUMMER>
<ANTAL>2</ANTAL>
<PRIS_ST>8727</PRIS_ST>
</RAD>
<RAD>
<ARTIKELNAMN>Drumguish</ARTIKELNAMN>
<ARTIKELNUMMER>278</ARTIKELNUMMER>
<ANTAL>5</ANTAL>
<PRIS_ST>249</PRIS_ST>
</RAD>
</FAKTURA_RADER>
</FAKTURA>

I want to import it the row ORGNR into the field lev_orgnr in table faktura aswell as KÖPARE/FÖRETAGSNAMN into kopare, FAKTURA_NR into faktura_nr etc. and also based on the FÖRETAGSNAMN I need an ID which is in another table and therefore I need to make an INNER JOIN but I have no idea where to start really.

Take this for example:

DECLARE @xmlDoc XML
SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK 'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_1.xml', SINGLE_BLOB
	) AS xmlData
	)
	

	SELECT 
	@xmlDoc.query('/FAKTURA/FÖRETAG/ORGNR'),
	@xmlDoc.query('/FAKTURA/KÖPARE/FÖRETAGSNAMN')

I get some data however with the tags (< and >).

If I use this badboy:

DECLARE @idoc int
DECLARE @doc xml
SET @doc =
(
SELECT * FROM OPENROWSET (
	BULK 'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_1.xml', SINGLE_BLOB
	) AS xmlData
	)
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT    *
FROM       OPENXML (@idoc, '/FAKTURA/FÖRETAG/NAMN',1)

I get a lot of fields but not really sure how to work with that data. Also it seems very "blurrish" if I need to do that statement for every row that I want to import.

Please help me understand and guide me to a solution to my problem!

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 01:24:11

use like


DECLARE @xmlDoc XML
SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK 'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_1.xml', SINGLE_BLOB
	) AS xmlData
	)
	


select t.u.value('(./FÖRETAG/NAMN)[1]','varchar(100)') as Namn,
t.u.value('(./FÖRETAG/ORGNR)[1]','varchar(100)') as ORGNR,
t.u.value('(./FÖRETAG/ADRESS)[1]','varchar(100)') as ADRESS,
t.u.value('(./FÖRETAG/POSTNUMMER)[1]','varchar(100)') as POSTNUMMER,
t.u.value('(./FÖRETAG/ORT)[1]','varchar(100)') as ORT,
t.u.value('(./FÖRETAG/TELEFON)[1]','varchar(100)') as TELEFON
FROM @xmlDoc.nodes('/FAKTURA')t(u)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 01:47:47

here's how you get whole columns


select t.u.value('(./FÖRETAG/NAMN)[1]','varchar(100)') as Namn,
t.u.value('(./FÖRETAG/ORGNR)[1]','varchar(100)') as ORGNR,
t.u.value('(./FÖRETAG/ADRESS)[1]','varchar(100)') as ADRESS,
t.u.value('(./FÖRETAG/POSTNUMMER)[1]','varchar(100)') as POSTNUMMER,
t.u.value('(./FÖRETAG/ORT)[1]','varchar(100)') as ORT,
t.u.value('(./FÖRETAG/TELEFON)[1]','varchar(100)') as TELEFON,
t.u.value('(./FÖRETAG/POSTGIRO)[1]','varchar(100)') as POSTGIRO,
t.u.value('(./KÖPARE/FÖRETAGSNAMN)[1]','varchar(100)') as FÖRETAGSNAMN,
t.u.value('(./KÖPARE/BESTÄLLARE)[1]','varchar(100)') as BESTÄLLARE,
t.u.value('(./FAKTURA_DETALJER/FAKTURA_NR)[1]','varchar(100)') as FAKTURA_NR,
t.u.value('(./FAKTURA_DETALJER/DATUM)[1]','date') as DATUM,
t.u.value('(./FAKTURA_DETALJER/TOTAL_SUMMA)[1]','int') as TOTAL_SUMMA,
t.u.value('(./FAKTURA_DETALJER/TOTAL_MOMS)[1]','varchar(100)') as TOTAL_MOMS,
m.n.value('(./ARTIKELNAMN)[1]','varchar(100)') as ARTIKELNAMN,
m.n.value('(./ARTIKELNUMMER)[1]','varchar(100)') as ARTIKELNUMMER,
m.n.value('(./ANTAL)[1]','int') as ANTAL,
m.n.value('(./PRIS_ST)[1]','int') as PRIS_ST
FROM @xmlDoc.nodes('/FAKTURA')t(u)
CROSS APPLY t.u.nodes('FAKTURA_RADER/RAD')m(n)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunnyleoneo
Starting Member

19 Posts

Posted - 2011-12-13 : 01:52:31

Hi,

Check this simple way to import xml file to sql table. Hope this will help you.
http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 01:56:26

quote:
Originally posted by sunnyleoneo

Hi,

Check this simple way to import xml file to sql table. Hope this will help you.
http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html

OP has already posted way to import xml to table
As I understand the difficulty was in getting the data within xml nodes onto tables which is what I've given suggestion for

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kfluffie
Posting Yak Master

103 Posts

Posted - 2011-12-13 : 02:06:36

Hi Visakh,
wise as always!

I figured it out after some morereading however XML is different and strange (my own oppinion) in MSSQL.

Here is what I wrote:

BEGIN TRANSACTION
DECLARE @xmlDoc XML

SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK 'D:\Studier\MCTS\ML326C - XML i Relationsdatabaser\Projektfiler\faktura_10.xml', SINGLE_BLOB
	) AS xmlData
	)

	INSERT INTO Faktura.XMLFaktura(faktura)
	SELECT @xmlDoc
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL'
		RETURN
	END
	
	
	
	/*Faktura_ID ska sättas in ifall allt gick bra ifrån XMLFaktura till faktura_id*/
	INSERT INTO Faktura.Faktura(faktura_id, lev_orgnr, kopare, anst_id, fakturanr, datum, totalsum,totalmoms)
	SELECT
	SCOPE_IDENTITY(), 
	@xmlDoc.value('(/FAKTURA/FÖRETAG/ORGNR)[1]', 'char(15)'),
	@xmlDoc.value('(/FAKTURA/KÖPARE/FÖRETAGSNAMN)[1]', 'char(10)'),
	--@xmlDoc.value('(/FAKTURA/KÖPARE/BESTÄLLARE)[1]', 'char(41)') as namnXML,
	(SELECT top 1 anst_id FROM HR.Anställd
		WHERE LTRIM(RTRIM(fornamn)) + ' ' + LTRIM(RTRIM(efternamn)) = @xmlDoc.value('(/FAKTURA/KÖPARE/BESTÄLLARE)[1]', 'CHAR(41)')),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/FAKTURA_NR)[1]', 'int'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/DATUM)[1]', 'smalldatetime'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_SUMMA)[1]', 'char(20)'),
	@xmlDoc.value('(/FAKTURA/FAKTURA_DETALJER/TOTAL_MOMS)[1]', 'char(20)')
	
	IF @@ERROR <> 0 
	BEGIN
		ROLLBACK TRANSACTION
		PRINT 'FAIL2'
		RETURN
	END
	
	
		COMMIT TRANSACTION
		PRINT 'Faktura importerad!'

Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources