July 1, 2010 at 7:21 am
Hello community,
I am kind of hopeless, because I am looking for a solution for this problem for a few days already without any success...
The problem:
I have an XML file on my hard drive and want to read its value and save it in a table on a db ms sql 2000. I think it is not possible to save the whole file there but that'd be enough if I just could read the value of each node.
Here is a small example of my file:
<Order>
<CustomerID>ALFKI</CustomerID>
<EmployeeID>3</EmployeeID>
<OrderDate>07/3/2004</OrderDate>
<RequiredDate>07/4/2004</RequiredDate>
<ShippedDate>15/3/2004</ShippedDate>
</Order>
So here I'd need the values: ALFKI, 3, 07/3/2004 and so on...
I'd be very gratefull for any advice!
July 1, 2010 at 11:08 am
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/
July 1, 2010 at 8:33 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply