Read XML file and create table in SQL Server 2000

[machilu]

Hi

I have a situation, where I need to read a xml file from the disk (c:\xml\test.xml) and create a table(just the schema) in SQL Server 2000.

The XML file has the customer info (Name, address, phone etc..). Is there a way to read this file and automatically create the customer table in SQL Server 2000?

Appreciate your response.

Thanks
Machilu

[skhanal]

Extending this example from BOL

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</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 * INTO MyTable
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
WHERE 0=1

--------
You can modify the SELECT statement to match the columns you want.