insert XML data into MS SQL 2000

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can use the openxml function. Below is a code that works with some explanations, but I suggest that you’ll read about in BOL (Books On Line).

declare @xml varchar(500)

declare @handle int

set @xml =

'<Order>

<CustomerID>ALFKI</CustomerID>

<EmployeeID>3</EmployeeID>

<OrderDate>07/3/2004</OrderDate>

<RequiredDate>07/4/2004</RequiredDate>

<ShippedDate>15/3/2004</ShippedDate>

</Order>'

--Parsing the XML and storing the handle

--of the memory structure that stores the parsed XML

--in @handle varible

EXEC sp_xml_preparedocument @handle OUTPUT, @xml

--Using the function openxml. Since this is a plain

--xml, I could use the thired parameter in the openxml

--function instead of describing for each tag it's position

--in the XML and if it is an attribute or element.

select *

from openxml (@handle, '/Order', 2) with

(CustomerID varchar(10),

EmployeeID smallint,

OrderDate varchar(10),

RequiredDate varchar(10),

ShippedDate varchar(10))

--Cleaning the memory from the parsed xml.

--It is very important to clean it. If you

--won't do it, it will be cleaned only when you

--close the connection to the server

EXEC sp_xml_removedocument @handle

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sergiy SSC Guru Points: 110208 More actions · Answer 2

And though you cannot declare a variable longer than 8000 characters in SQL2000 you still can declare a SP parameter with type "text" ot "ntext".

Then you can pass the whole file (if it's less than 2GB) to that procedure as a parameter and parse it there using the code from Adi.

_____________
Code for TallyGenerator