Importing an XML file into SQL2K table

Is it possible to import an XML file into a SQL 2000 table? I can't seem to
find any white pages on this.
TIA,
JoeHi,
use OPENXML function
"jaylou" wrote:

> Is it possible to import an XML file into a SQL 2000 table? I can't seem
to
> find any white pages on this.
> TIA,
> Joe
>
>|||Exemple:
declare @.idoc int
declare @.doc varchar(1000)
set @.doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- SELECT statement using OPENXML rowset provider
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @.idoc
I hope to help you.
"jaylou" wrote:

> Is it possible to import an XML file into a SQL 2000 table? I can't seem
to
> find any white pages on this.
> TIA,
> Joe
>
>|||thank you,
I was trying this but my files were larger then 8000 characters and
sp_xml_preparedocument choked evertime I tried.
I had to use FSO to get the file into a global variable then I called a
stored proc to run your attached code.
Thanks again,
joe
"Dio" wrote:
> Exemple:
> declare @.idoc int
> declare @.doc varchar(1000)
> set @.doc ='
> <ROOT>
> <Customers CustomerID="VINET" ContactName="Paul Henriot">
> <Orders CustomerID="VINET" EmployeeID="5" OrderDate=
> "1996-07-04T00:00:00">
> <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
> <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
> </Orders>
> </Customers>
> <Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
> "1996-08-16T00:00:00">
> <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
> </Orders>
> </Customers>
> </ROOT>'
> --Create an internal representation of the XML document.
> exec sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- SELECT statement using OPENXML rowset provider
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customers')
> EXEC sp_xml_removedocument @.idoc
> I hope to help you.
> "jaylou" wrote:
>|||http://support.microsoft.com/?scid=316005|||Hi, "jaylou"!
Try using "NTEXT" datatype to handle XML strings. Here's an extract for
NTEXT from SQL Server 2000 Books Online:
Variable-length Unicode data with a maximum length of 230 - 1
(1,073,741,823) characters.
So, you have a max possible length of 1,073,741,823 characters available for
your XML...Enjoy!!
Thanks & Regards,
Nakul Vachhrajani
"jaylou" wrote:
> thank you,
> I was trying this but my files were larger then 8000 characters and
> sp_xml_preparedocument choked evertime I tried.
> I had to use FSO to get the file into a global variable then I called a
> stored proc to run your attached code.
> Thanks again,
> joe
>
> "Dio" wrote:
>