How can i read an xml file from SQLServer

I need to know how to read an xml file using SQL. (in SQLServer)
Then i will manipulate the data and update my tables.
May be i can put the xml file in the IIS root.
Then what is required for the rest..
Thanks in advance
BennyOriginally posted by bennydubai
I need to know how to read an xml file using SQL. (in SQLServer)
Then i will manipulate the data and update my tables.

May be i can put the xml file in the IIS root.
Then what is required for the rest..

Thanks in advance

Benny

Check openXML and other XML related topics in BOL,

Here is a sample 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 *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))|||I understood what u meant in OPENXML.
But how do we physically read from an XML file in that case

Thanks in advance|||Originally posted by bennydubai
I understood what u meant in OPENXML.
But how do we physically read from an XML file in that case

Thanks in advance

i have not worked on it, but u can pass the xml as a parameter to the stored proc or dts it in a table.
i think after u dts the raw xml in a table u can read and manipulate it easily.|||I think the only way to read from XML file to the database is by using vbscript and calling some SQLXMLBulkLoad commands..
Let me know if there are better ways please...

And how do we write an xml ( that we get in the for xml clause) in to a file?

Please help