August 31, 2009 at 10:45 am
Can somebody help to import xml file to a tabel using TSQL
- no SSIS and dynamic SQL is allowed (client doesn't wan this 2)
how can it be done using pure TSQL
Thanks,
August 31, 2009 at 12:12 pm
Is the name of the file containing the XML going to be the same every time?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 31, 2009 at 12:52 pm
Yes, it is the same every time ( the table name is also the same every time)
Thanks
August 31, 2009 at 1:05 pm
This will illustrate using OPENROWSET to read the contents of the XML file.
Obviously you could take the FROM OPENROWSET and use it to insert a row into a table, update a column in a table, etc.
-- just change the directory and file name to point to your file to be loaded
DECLARE @xml as xml
SELECT @xml = BulkColumn FROM OPENROWSET(BULK N'c:\sample.xml', SINGLE_CLOB) as a ;
SELECT @xml
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 1:48 pm
Thank you Bob, you post helped me a lot
I was using this query to read from the file, it is taking so much time, do you kow what the problem could be;
select top 2 returns the right data; but when I do for the whole it is taking long ( the xml file is 1993 rows of data
see code and sample xml file
--code
SELECT X.legacy_data.query('ID').value('.', 'INT'),
X.legacy_data.query('PROT').value('.', 'VARCHAR(20)'),
X.legacy_data.query('DOC').value('.', 'VARCHAR(30)'),
X.legacy_data.query('S_NUMBER').value('.', 'VARCHAR(20)'),
X.legacy_data.query('S_ID').value('.', 'VARCHAR(20)'),
X.legacy_data.query('last_name').value('.', 'VARCHAR(45)'),
X.legacy_data.query('first_name').value('.', 'VARCHAR(35)'),
X.legacy_data.query('middle_name').value('.', 'VARCHAR(35)'),
X.legacy_data.query('PER_ID').value('.', 'VARCHAR(30)'),
X.legacy_data.query('MIS_ID').value('.', 'VARCHAR(30)'),
X.legacy_data.query('invest_id').value('.', 'VARCHAR(30)'),
X.legacy_data.query('VERSION').value('.', 'VARCHAR(20)'),
X.legacy_data.query('REGISTRATION_DATE').value('.', 'VARCHAR(50)')
FROM
(SELECT CAST(x AS XML)
FROM OPENROWSET(BULK 'C:\legacy_data.xml', SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('ROOT/legacy_data') AS X(legacy_data);
--sample data
-
-
1
10629
10629
31524
31524
Gwadz
Marya
Viorst
102713
900017303
32166
2
2008-05-27T00:00:00
September 1, 2009 at 2:11 pm
Hi washawgolla
Try to avoid the query-method. You can use value() directly:
DECLARE @xml XML
SELECT @xml = '
1
10629
10629
31524
31524
Gwadz
Marya
Viorst
102713
900017303
32166
2
2008-05-27T00:00:00
'
--code
SELECT x.legacy_data.value('(./id/text())[1]', 'int'),
x.legacy_data.value('(./prot/text())[1]', 'varchar(20)'),
x.legacy_data.value('(./doc/text())[1]', 'varchar(30)'),
x.legacy_data.value('(./s_number/text())[1]', 'varchar(20)'),
x.legacy_data.value('(./s_id/text())[1]', 'varchar(20)'),
x.legacy_data.value('(./last_name/text())[1]', 'varchar(45)'),
x.legacy_data.value('(./first_name/text())[1]', 'varchar(35)'),
x.legacy_data.value('(./middle_name/text())[1]', 'varchar(35)'),
x.legacy_data.value('(./per_id/text())[1]', 'varchar(30)'),
x.legacy_data.value('(./mis_id/text())[1]', 'varchar(30)'),
x.legacy_data.value('(./invest_id/text())[1]', 'varchar(30)'),
x.legacy_data.value('(./version/text())[1]', 'varchar(20)'),
x.legacy_data.value('(./registration_date/text())[1]', 'varchar(50)')
FROM @xml.nodes('root/legacy_data') AS X(legacy_data);
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
September 1, 2009 at 2:36 pm
Thank you very much Florian;
it solved my problem; it took it down to 6 sec's from 30 minutes
five star
*****
Thanks
September 1, 2009 at 2:41 pm
Glad we could help! 🙂
Thanks for the feedback
How to Post Data/Code to get the best Help How to Post Performance Problems
September 1, 2009 at 2:49 pm
Truly, you are the man, Flo. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 2:56 pm
Bob Hovious (9/1/2009)
Truly, you are the man, Flo. 😉
And you stay my mentor. 🙂
How to Post Data/Code to get the best Help How to Post Performance Problems
September 1, 2009 at 2:58 pm
Hardly. I just get a lot of mileage out of stolen techniques and some lucky guesses. 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 3:10 pm
Everybody of us uses techniques stolen from somebody else. From a book, a thread, a blog or the guy next to us at work.
It's not just the answers you give, it's the kind of questions you ask ;-). Your last question in this thread took me about three days researching and studying different books. Sadly without a satisfiable answer...
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply