Question about importing XML data

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

Question about importing XML data

Author

Topic

V.V.
Starting Member

32 Posts

Posted - 2012-09-17 : 07:09:50

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 08:31:40

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)

V.V.
Starting Member

32 Posts

Posted - 2012-09-17 : 09:23:31

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 09:58:38

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)

V.V.
Starting Member

32 Posts

Posted - 2012-09-17 : 10:12:04

Thank you for explaining me.

No, it doesn't work. No rows were imported.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 10:18:49

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)

V.V.
Starting Member

32 Posts

Posted - 2012-09-17 : 10:36:52

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

V.V.
Starting Member

32 Posts

Posted - 2013-03-12 : 07:03:48

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?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 07:16:02

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 07:34:24

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

V.V.
Starting Member

32 Posts

Posted - 2013-03-13 : 05:22:37

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 06:34:10

Can you post the error message?

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources