SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question about importing XML data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

V.V.
Starting Member

30 Posts

Posted - 09/17/2012 :  07:09:50  Show Profile  Reply with Quote
Hello!

I want to import an XML file with more than 4000 lines into SQL Server. The script for importing is this:


DECLARE @xmlDoc XML
SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK 'C:\!a\test.xml', SINGLE_BLOB
	) AS xmlData
	)
	
INSERT INTO tpl_anx1a (cmd, id_er, id_fp, date_val, cod_op, cnp, nume, prenume, cb, data_cb, cn, data_conv, nuf_cn, sold_uf)

SELECT 
       t.u.value('(./RECORD/cmd)[1]', 'INT') as cmd,
       t.u.value('(./RECORD/id_er)[1]', 'varchar(24)') as id_er,
       t.u.value('(./RECORD/id_fp)[1]', 'varchar(24)') as id_fp,      
       t.u.value('(./RECORD/date_val)[1]', 'varchar(24)') as date_val, 
       t.u.value('(./RECORD/cod_op)[1]', 'INT') as cod_op,
       t.u.value('(./RECORD/cnp)[1]', 'varchar(24)') as cnp,
       t.u.value('(./RECORD/nume)[1]', 'varchar(50)') as nume,
       t.u.value('(./RECORD/prenume)[1]', 'varchar(50)') as prenume,
       t.u.value('(./RECORD/cb)[1]', 'decimal(14,2)') as cb,
       t.u.value('(./RECORD/data_cb)[1]', 'varchar(24)') as data_cb,
       t.u.value('(./RECORD/cn)[1]', 'decimal(14,2)') as cn,
       t.u.value('(./RECORD/data_conv)[1]', 'varchar(24)') as data_conv,
       t.u.value('(./RECORD/nuf_cn)[1]', 'decimal(16,6)') as nuf_cn,
       t.u.value('(./RECORD/sold_uf)[1]', 'decimal(16,6)') as sold_uf
FROM  @xmlDoc.nodes('/RECORDS')t(u)     


Ok guys, this imports me just a row. What should I modify in the script to import all the lines?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/17/2012 :  08:31:40  Show Profile  Reply with Quote
Add another virtual table like shown below and query against that:
SELECT 
       c2.value('(./cmd)[1]', 'INT') as cmd,
       c2.value('(./id_er)[1]', 'varchar(24)') as id_er,
       ....
FROM  @xmlDoc.nodes('/RECORDS')t(u) 
	CROSS APPLY u.nodes('/RECORD') t2(c2)
Go to Top of Page

V.V.
Starting Member

30 Posts

Posted - 09/17/2012 :  09:23:31  Show Profile  Reply with Quote
#sunitabeck, the script should look like this?


DECLARE @xmlDoc XML
SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK 'C:\!a\test.xml', SINGLE_BLOB
	) AS xmlData
	)
	
INSERT INTO tpl_anx1a (cmd, id_er, id_fp, date_val, cod_op, cnp, nume, prenume, cb, data_cb, cn, data_conv, nuf_cn, sold_uf)

SELECT 
       t.u.value('(./RECORD/cmd)[1]', 'INT') as cmd,
       t.u.value('(./RECORD/id_er)[1]', 'varchar(24)') as id_er,
       t.u.value('(./RECORD/id_fp)[1]', 'varchar(24)') as id_fp,      
       t.u.value('(./RECORD/date_val)[1]', 'varchar(24)') as date_val, 
       t.u.value('(./RECORD/cod_op)[1]', 'INT') as cod_op,
       t.u.value('(./RECORD/cnp)[1]', 'varchar(24)') as cnp,
       t.u.value('(./RECORD/nume)[1]', 'varchar(50)') as nume,
       t.u.value('(./RECORD/prenume)[1]', 'varchar(50)') as prenume,
       t.u.value('(./RECORD/cb)[1]', 'decimal(14,2)') as cb,
       t.u.value('(./RECORD/data_cb)[1]', 'varchar(24)') as data_cb,
       t.u.value('(./RECORD/cn)[1]', 'decimal(14,2)') as cn,
       t.u.value('(./RECORD/data_conv)[1]', 'varchar(24)') as data_conv,
       t.u.value('(./RECORD/nuf_cn)[1]', 'decimal(16,6)') as nuf_cn,
       t.u.value('(./RECORD/sold_uf)[1]', 'decimal(16,6)') as sold_uf
FROM  @xmlDoc.nodes('/RECORDS')t(u)

SELECT 
       c2.value('(./cmd)[1]', 'INT') as cmd,
       c2.value('(./id_er)[1]', 'varchar(24)') as id_er,
       c2.value('(./id_fp)[1]', 'varchar(24)') as id_fp,      
       c2.value('(./date_val)[1]', 'varchar(24)') as date_val, 
       c2.value('(./cod_op)[1]', 'INT') as cod_op,
       c2.value('(./cnp)[1]', 'varchar(24)') as cnp,
       c2.value('(./nume)[1]', 'varchar(50)') as nume,
       c2.value('(./prenume)[1]', 'varchar(50)') as prenume,
       c2.value('(./cb)[1]', 'decimal(14,2)') as cb,
       c2.value('(./data_cb)[1]', 'varchar(24)') as data_cb,
       c2.value('(./cn)[1]', 'decimal(14,2)') as cn,
       c2.value('(./data_conv)[1]', 'varchar(24)') as data_conv,
       c2.value('(./nuf_cn)[1]', 'decimal(16,6)') as nuf_cn,
       c2.value('(./sold_uf)[1]', 'decimal(16,6)') as sold_uf
FROM  @xmlDoc.nodes('/RECORDS')t(u) 
	CROSS APPLY u.nodes('/RECORD') t2(c2)  


Sorry, I'm new on XML.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/17/2012 :  09:58:38  Show Profile  Reply with Quote
No, I meant just this. Run the query after commenting out the INSERT INTO line and see what you get. If that is to your liking, then uncomment the INSERT INTO line also to do the inserts.
DECLARE @xmlDoc XML
SET @xmlDoc =(
SELECT * FROM OPENROWSET (
	BULK 'C:\!a\test.xml', SINGLE_BLOB
	) AS xmlData
	)
	
INSERT INTO tpl_anx1a (cmd, id_er, id_fp, date_val, cod_op, cnp, nume, prenume, cb, data_cb, cn, data_conv, nuf_cn, sold_uf)
SELECT 
       c2.value('(./cmd)[1]', 'INT') as cmd,
       c2.value('(./id_er)[1]', 'varchar(24)') as id_er,
       c2.value('(./id_fp)[1]', 'varchar(24)') as id_fp,      
       c2.value('(./date_val)[1]', 'varchar(24)') as date_val, 
       c2.value('(./cod_op)[1]', 'INT') as cod_op,
       c2.value('(./cnp)[1]', 'varchar(24)') as cnp,
       c2.value('(./nume)[1]', 'varchar(50)') as nume,
       c2.value('(./prenume)[1]', 'varchar(50)') as prenume,
       c2.value('(./cb)[1]', 'decimal(14,2)') as cb,
       c2.value('(./data_cb)[1]', 'varchar(24)') as data_cb,
       c2.value('(./cn)[1]', 'decimal(14,2)') as cn,
       c2.value('(./data_conv)[1]', 'varchar(24)') as data_conv,
       c2.value('(./nuf_cn)[1]', 'decimal(16,6)') as nuf_cn,
       c2.value('(./sold_uf)[1]', 'decimal(16,6)') as sold_uf
FROM  @xmlDoc.nodes('/RECORDS')t(u) 
	CROSS APPLY u.nodes('/RECORD') t2(c2)
Go to Top of Page

V.V.
Starting Member

30 Posts

Posted - 09/17/2012 :  10:12:04  Show Profile  Reply with Quote
Thank you for explaining me.

No, it doesn't work. No rows were imported.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/17/2012 :  10:18:49  Show Profile  Reply with Quote
I had a typo in the code (see in red on the last line). I am assuming that your structure is something like I am showing in the example below.
DECLARE @xmlDoc  XML;

SET @xmlDoc  = '
<RECORDS>
  <RECORD>
    <cmd>32</cmd>
    <id_er>ID1</id_er>
  </RECORD>
  <RECORD>
    <cmd>44</cmd>
    <id_er>ID2</id_er>
  </RECORD>
</RECORDS>'

SELECT 
       c2.value('(./cmd)[1]', 'INT') as cmd,
       c2.value('(./id_er)[1]', 'varchar(24)') as id_er
FROM  @xmlDoc.nodes('/RECORDS')t(u) 
	CROSS APPLY u.nodes('./RECORD') t2(c2)
Go to Top of Page

V.V.
Starting Member

30 Posts

Posted - 09/17/2012 :  10:36:52  Show Profile  Reply with Quote
#sunitabeck, thanks a lot. It works like a charm for the file with 4700 lines.

Now I'm trying to import into a table another XML file with more than 550000 rows. I'm wonder if it's working with such a large file...

...and it's working!

Thanks!

Edited by - V.V. on 09/17/2012 10:39:13
Go to Top of Page

V.V.
Starting Member

30 Posts

Posted - 03/12/2013 :  07:03:48  Show Profile  Reply with Quote
Guys, I'm coming back with an issue. I want to import a XML file using #sunitabeck's script and I received the following message:

Msg 9420, Level 16, State 1, Line 2
XML parsing: line 1, character 100496860, illegal xml character

It's very hard to open the xml file and see which character is the problem because it has like 180MB (over 560000 rows). Can you help me with an idea on this?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/12/2013 :  07:16:02  Show Profile  Reply with Quote
XML parsing will fail for some special characteers such as & etc....

one more suggestion is
XML parsing: line 1, character 100496860 , illegal xml character

Find the character in the position 100496860 by opening that file.. then you will let you know which character is causing problem

Example:
declare @t table(col xml)
insert @t select '<a>&</a>'

-- error like this:
Msg 9421, Level 16, State 1, Line 3
XML parsing: line 1, character 5, illegal name character
Length of <a>& is 4...

--
Chandu

Edited by - bandi on 03/12/2013 07:18:06
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/12/2013 :  07:34:24  Show Profile  Reply with Quote
solutions for above error......
declare @XmlResponse as xml;
select @XmlResponse = '<?xml version="1.0" encoding="utf-8"?><Response>©</Response>'
select @XmlResponse as myxml

--Method1: You can replace the © symbol with the XML entity & #169;

declare @XmlResponse as xml;
select @XmlResponse = REPLACE('<?xml version="1.0" encoding="utf-8"?><Response>#169;</Response>', '©', ' & #169;')
select @XmlResponse as myxml

--Best way: Problem is with encoding attribute (Eg: encoding="utf-16") . So it will work if you remove it or change it to utf-16.
declare @xml nvarchar(max) =
'<?xml version="1.0" encoding="utf-16" ?><Response>©</Response>'
select convert(xml, @xml) myxml

--
Chandu

Edited by - bandi on 03/12/2013 07:40:31
Go to Top of Page

V.V.
Starting Member

30 Posts

Posted - 03/13/2013 :  05:22:37  Show Profile  Reply with Quote
#bandi, thank you for your answers.

I opened the .csv format of the xml file and I saw on couple of rows the existence of 'NULL' (as text) where should be decimal. So, I thought that was the problem and I modified the structure of the table into VARCHAR and also NVARCHAR for all columns. Same error.

My question is: could be this the problem (having NULL on decimal columns in xml file)? If so, why didn't import me the file when I've changed the structure of the table?



Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 03/14/2013 :  06:34:10  Show Profile  Reply with Quote
Can you post the error message?

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000