November 7, 2003 at 2:28 pm
Hello,
I have xml files that descript data structures. How can I generate tables (SQL Server 2000) by using these XML files?
Thank you!
November 10, 2003 at 8:00 am
This was removed by the editor as SPAM
November 10, 2003 at 8:22 am
See the following code....
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe"
CompanyName="Company1">
<Orders CustomerID="XYZAA"
OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA"
OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@hDoc, N'/ROOT/Customers')
WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@hDoc, N'//Orders')
WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Customers/Orders') with (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @hDoc
WHETHER YOU ARE TRYING TO READ FROM A FILE ???
Linto
November 10, 2003 at 8:25 am
See the following Link to see more details based on an XML FILE...
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20670044.html
Linto
Edited by - linto on 11/10/2003 08:25:47 AM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply