Assuming you're using SQL Server 2000, you could use SQLXML 3.0
(
http://www.microsoft.com/downloads/d...33A9-CF10-4E22
-8004-477098A407AC&displaylang=en).
There's a couple of ways you could do this. The first is to annotate a
schema and use the SQLXMLBULKLOAD component - this is a COM-based component
that can be used to import XML into SQL Server tables based on the mappings
defined in an annotated schema. See
http://msdn.microsoft.com/library/de...kload_6bos.asp
for an example of using SQLXMLKBULKLOAD.
The other alternative is to use the OPENXML function. The idea here is to
create a SProc to which
you'll pass your XML (usually as an NTEXT parameter). In the SProc you use
sp_xml_preparedocument to generate an in-memory "DOM-like" tree structure,
and then use OPENXML to shred the XML into relational rowsets. Below is a
simple example that might help you get started (search for "Using OPENXML"
in BOL and you'll find some examples there.)
The OPENXML syntax is a little complex so here's a brief explanation:
SELECT * FROM
OPENXML(<DOM-Tree created by sp_prepare_document>,
<XPath identifying the node in the document where you
want to start the query>,
<flags: 1=search attributes by default, 2=return
subelements by default, 3 = return both attributes and subelements by
default> )
WITH (<Name of a table that matches the resultset you want | Rowset
definition consisting of column name, type, and optionally an XPath
expression if the data won't be returned by the Flags in the OPENXML clause
(the XPath is relative to the starting point specified in the OPENXML
clause)
-- Create Temporary Tables to simulate the order and order detail tables
CREATE TABLE #Orders
(OrderID INTEGER NOT NULL ,
CustomerID nCHAR (5) NULL ,
EmployeeID INTEGER NULL ,
OrderDate DATETIME NULL)
CREATE TABLE #OrderDetails
( OrderID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
UnitPrice MONEY NOT NULL,
Quantity SMALLINT NOT NULL,
Discount REAL NOT NULL )
GO
DECLARE @iTree INTEGER
DECLARE @xmlOrder VARCHAR(2000)
-- Simulate an order
SET @xmlOrder = '
<?xml version="1.0"?>
<Order OrderID="1001" CustomerID="ALFKI" EmployeeID="1"
OrderDate="01/01/2001">
<Items>
<Item ProductID="11" Qty="1" UnitPrice="12.99">
<Discount>0</Discount>
</Item>
<Item ProductID="17" Qty="2" UnitPrice="4.99">
<Discount>0.5</Discount>
</Item>
<Item ProductID="21" Qty="1" UnitPrice="11.99">
<Discount>0</Discount>
</Item>
</Items>
</Order>
'
EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder
--Insert Order Record
INSERT #Orders (OrderID, CustomerID, EmployeeID, OrderDate)
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM
OPENXML(@iTree, 'Order', 1)
WITH ( OrderID INTEGER,
EmployeeID INTEGER,
OrderDate DATETIME,
CustomerID nCHAR(5))
-- Insert Order Details Record
INSERT #OrderDetails
SELECT * FROM
OPENXML(@iTree, 'Order/Items/Item', 1)
WITH ( OrderID INTEGER '../../@OrderID',
ProductID INTEGER,
Qty INTEGER,
UnitPrice MONEY,
Discount REAL 'Discount')
-- Clear the XML from memory
EXEC sp_xml_removedocument @iTree
GO
SELECT * FROM #Orders
SELECT * FROM #OrderDetails
Hope that helps.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"maltchev" <ma******@yahoo.com> wrote in message
news:4Cxvb.451547$pl3.385500@pd7tw3no...
i need to insert data from an xml file into sql server table.
the xml file contains only one record.
how to insert the data? how to map the names of the fields in the xml file
and the table?
thank you in advance.
