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!

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

HOW can I Import XML file into SQL Server 2000

Status
Not open for further replies.

Ranvier

Programmer
Jun 17, 2004
73
0
0
GB
Hi,

I am trying to import an XML file into SQL server 2000. Can someone tell me how to do this. Eventually i want to automate the import in DTS.

Thank you

My file format is:


<?xml version="1.0" encoding="UTF-8"?>
<!-- created by FLARE using ascxml.q from ascii file -->

<Notices>
<Notice>
<prref>Q01PCI359-/1</prref>
<type>S08</type>
</Notice>
<Notice>
<prref>R18DFA39BB/1</prref>
<type>S08</type>
</Notice>
<Notice>
<prref>P05GCO5PAR/1</prref>
<type>T13</type>
</Notice>
<Notice>
<prref>R02GCE27ST/1</prref>
<type>T13</type>
</Notice>
</Notices>
 
I would suggest that you create a stored procedure to import this data. The stored procedure would look something like this...

Code:
Alter Procedure ImportXMLData
	@XML Text
As
SET NOCOUNT ON

Declare @iDoc Int
Exec sp_xml_preparedocument @iDoc OUTPUT, @XML

Select *
From   OpenXML(@iDoc, '//Notices/Notice', 2)
With   (
       prref varchar(100),
       type varchar(100)
       )

Of course, this procedure will only select the data, but it would be simple enough to convert it so Insert Into....

You could test the procedure like this...

Code:
ImportXMLData '
<?xml version="1.0" encoding="UTF-8"?>
<!-- created by FLARE using ascxml.q from ascii file -->

<Notices>
    <Notice>
        <prref>Q01PCI359-/1</prref>
        <type>S08</type>
    </Notice>
    <Notice>
        <prref>R18DFA39BB/1</prref>
        <type>S08</type>
    </Notice>
    <Notice>
        <prref>P05GCO5PAR/1</prref>
        <type>T13</type>
    </Notice>
    <Notice>
        <prref>R02GCE27ST/1</prref>
        <type>T13</type>
    </Notice>
</Notices>'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
I use this for a stored procedure template but if you google seach 'Insert OpenXML' and you can't go wrong.


Code:
CREATE  PROCEDURE sp_insert_appointments_openxml
    @strXML ntext
AS
	DECLARE @iDoc int
	EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML
	INSERT INTO tbl_appointments (appointments_profileID, appointments_displaytime, appointments_description, appointments_datetime)
	(SELECT * FROM OPENXML(@iDoc, '/tbl_appointments/row', 3)
	WITH
	(
		appointments_profileID INT, 
		appointments_displaytime VARCHAR(50),
		appointments_description VARCHAR(50),
		appointments_datetime DATETIME)
      	)
    	EXECUTE sp_xml_removedocument @iDoc
 

Thanks for the help. My files turned out to be very large and so I used Visual Basic ActiveX Script to import the XML data in the end.

Thanks :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top