| Author |
 Topic  |
|
|
V.V.
Starting Member
29 Posts |
 Posted - 09/17/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/17/2012 : 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
29 Posts |
Posted - 09/17/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/17/2012 : 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
29 Posts |
Posted - 09/17/2012 : 10:12:04
|
Thank you for explaining me.
No, it doesn't work. No rows were imported. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/17/2012 : 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
29 Posts |
Posted - 09/17/2012 : 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! |
Edited by - V.V. on 09/17/2012 10:39:13 |
|
|
|
V.V.
Starting Member
29 Posts |
Posted - 03/12/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/12/2013 : 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 |
Edited by - bandi on 03/12/2013 07:18:06 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/12/2013 : 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 |
Edited by - bandi on 03/12/2013 07:40:31 |
|
|
|
V.V.
Starting Member
29 Posts |
Posted - 03/13/2013 : 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
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 03/14/2013 : 06:34:10
|
Can you post the error message?
--
Chandu |
|
|
| |
Topic |
|
|
|
Question about importing XML data
