Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing an XML file into SQL2K table

Status
Not open for further replies.
jaylou

jaylou

Programmer
Feb 17, 2005
70
US
Is there a way to import an XML file into a SQL2000 table? I can only find information on how to retrieve the XML data from a query.

I have tried:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='E:\XmlFiles\ENICOpen.xml'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/insurity /CSR',1)
WITH (ENICNo_Number varchar(10),
InsurityNumber varchar(20),
EffectiveDate datetime)

But I get this error:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 5
XML parsing error: Invalid at the top level of the document

TIA,
Joe
 
Sort by date Sort by votes
How XML file looks like? One root element?

Post few first lines here....

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
The file did not have a <root> So I added it. did I add it propperly?
<ROOT>
<CSR>
<ENICNo_Type>C</ENICNo_Type>
<ENICNo_Number>123456</ENICNo_Number>
<InsurityNumber>54822</InsurityNumber>
<LOB>WK</LOB>
<State>NN</State>
<EffectiveDate>2006-01-01</EffectiveDate>
<OriginalDescription>Blah blah blah</OriginalDescription>
<Status>BWO</Status>
<OrigTargetRelease>2005.3 SP12</OrigTargetRelease>
<CurrTargetRelease>2005.3 SP12</CurrTargetRelease>
<Comments>Blah blah blah</Comments>
</CSR>
</ROOT>
 
Upvote 0 Downvote
I thought XML file is invalid (with several root nodes).

No... forgetaboutit.

sp_xml_preparedocument accepts XML contents, not file name :(

And path '/insurity /CSR' is invalid - this means root node must be named <insurity> and btw. there is one extra trailing space.

And... I'm not sure attribute-centric mapping (1) will do the trick here.



------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
OK, a couple issues here that are needed for clarification. First, XML is case sensitive, so you need to be careful. Second, your OPENXML statement has a 3rd parameter of 1 which means attribute centric mapping, but clearly your XML is element centric. Third, you need to have the XML data, not a reference to a file. Based on your data, I am providing an example.

Code: 
DECLARE @idoc int
DECLARE @doc varchar(2000)
SET @doc ='
<ROOT>
<CSR>
<ENICNo_Type>C</ENICNo_Type>
<ENICNo_Number>123456</ENICNo_Number>
<InsurityNumber>54822</InsurityNumber>
<LOB>WK</LOB>
<State>NN</State>
<EffectiveDate>2006-01-01</EffectiveDate>
<OriginalDescription>Blah blah blah</OriginalDescription>
<Status>BWO</Status>
<OrigTargetRelease>2005.3 SP12</OrigTargetRelease>
<CurrTargetRelease>2005.3 SP12</CurrTargetRelease>
<Comments>Blah blah blah</Comments>
</CSR>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/CSR',2)
            WITH (ENICNo_Number  varchar(10),
                  InsurityNumber varchar(20),
          EffectiveDate datetime)

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Blargh... you were faster - my code is nearly identical :)

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
Last week I had a boo boo on the tip of my right index finger, so typing rate was cut in half. This week... I'm back to full functionality. Look out!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
OK, anyway...
Code: 
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT,  '
<CSR>
	<ENICNo_Type>C</ENICNo_Type>
	<ENICNo_Number>123456</ENICNo_Number>
	<InsurityNumber>54822</InsurityNumber>
	<LOB>WK</LOB>
	<State>NN</State>
	<EffectiveDate>2006-01-01</EffectiveDate>
	<OriginalDescription>Blah blah blah</OriginalDescription>
	<Status>BWO</Status>
	<OrigTargetRelease>2005.3 SP12</OrigTargetRelease>
	<CurrTargetRelease>2005.3 SP12</CurrTargetRelease>
	<Comments>Blah blah blah</Comments>
</CSR>'

SELECT    *
FROM       OPENXML (@idoc, '/CSR', 2)
            WITH (ENICNo_Number  varchar(10) ,
                  InsurityNumber varchar(20) ,
          EffectiveDate datetime )

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
ok, well here's the answer the the origional question.
Code: 
DECLARE @docHandle int
DECLARE @xmlDocument xml

create table #LoadingXML
(pkid int IDENTITY(1,1) primary key,
doc xml)

insert into #LoadingXML
select CONVERT(xml, BulkColumn, 2)
from OpenRowSet(bulk N'D:\path\to\file.xml',SINGLE_BLOB) as a

select @xmlDocument = doc
from #LoadingXML


EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

select *
from OPENXML(@docHandle, N'/ROOT/CSR', 2)  /*I'm using 2, you may need another option here*/

EXEC sp_xml_removedocument @docHandle 

drop table #LoadingXML

You may all revele in my code...

I use this same code (within a loop, and with the openrowset a dynamic SQL so that I can pass in a variable for the file name) to load files at the office.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Upvote 0 Downvote
>> Is there a way to import an XML file into a [!]SQL2000[/!] table?
...
> DECLARE @xmlDocument [!]xml[/!]

:(

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
Thanks for all the input.
Denny,
I get this error when I tried yours
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'bulk'.

Vongrunt,
I can execute yours as is, but I need to call a file from a directory. When I change the code to the path I get the same error I posted at the begining.

This is really driving me crazy.. Is there anything I am missing?

Thanks again,
Joe
 
Upvote 0 Downvote
jaylou,

I suppose the real issue is, How do I load the XML?

I haven't come up with an answer that will satisfy all conditions. Sorry. It is possible to load a file in to SQL Server using T-SQL syntax, but it becomes problematic if the file is larger than 8000 bytes. Problematic, but not impossible. My suggestion would be to creat a stored procedure that accepts a text parameter representing your XML document. In your front end app, you could use the file system object to load the XML from disk, and then send it in to the stored procedure for processing. In that case, the stored procedure would be:

Code: 
Create Procedure LoadXML
  @XML Text
As
Set Nocount On

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @XML

SELECT    *
FROM       OPENXML (@idoc, '/CSR', 2)
            WITH (ENICNo_Number  varchar(10) ,
                  InsurityNumber varchar(20) ,
          EffectiveDate datetime )

If your XML is always less than 8000 characters, you could load the data within the stored procedure, like so...

Code: 
Declare @XML VarChar(8000)

Create Table #Temp(Data varchar(8000))
Bulk insert #temp from 'C:\tektips.xml'
with (rowterminator = '')

Select @XML = Data From #Temp

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Thank you all. My files are lareger then 8000 characters so I will either need to create an EXE that will load the table or use the file system oblects to get it into the XML variable. I was trying to keep everything in one SP but oh well I will need to figure this out.

Does anyone have any sample FSO.

Thanks again,
Joe
 
Upvote 0 Downvote
ok, so I thought I was being all cool and stuff.

If you create a temp table with the text data type you should be able to stuff it into that, the prepair it as normal.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Upvote 0 Downvote
Thanks all!

I have created a DTS package that has an ActiveX script using FSO to import the file into a global variable then I created a simple SP that loads a table from the variable.

this all works great.

Thank again for all the input. I would never have gotten it without all your help.

Joe
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
Consolidate multiple rows into single row 1
Replies
4
Views
318
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
489
Chris Miller
Chris Miller
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
354
Volvere
V
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
330
BobSuruncle
BobSuruncle
eboughey1008
  • Locked
  • Question
Import columns from one table into another table with dissimilar field layout
Replies
7
Views
163
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top