March 20, 2012 at 10:37 pm
Dear Friends,
How can i transfer data from xml to sql server 2005 in a row set. i have complete parsing and shreading but when i am insert this data in a table on that error ocured. i can't understand this error. Please help me.
March 21, 2012 at 10:46 pm
Try using OPEN xml:
Prerequisites: You should be able to query using xpath. See the below example
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/Order/OrderDetail') with (OrderID int, ProductID int, Quantity int)
EXEC sp_xml_removedocument @idoc
Give your input xml at the set @doc.
Always remember to use EXEC sp_xml_removedocument as it's heavy object & costs lots of memory.
You can run this directly & see the output.
March 22, 2012 at 3:20 am
Dear Friend,
My another Question, can i partition a table in sql server 2005 standerd edition. When I have did this one Error message show "You can partition only the sql server 2005 enterprise Edition". Please Help me....
March 22, 2012 at 5:10 pm
samareshmaity (3/22/2012)
Dear Friend,My another Question, can i partition a table in sql server 2005 standerd edition. When I have did this one Error message show "You can partition only the sql server 2005 enterprise Edition". Please Help me....
Not on Standard Edition. From Partitioned Table and Index Concepts:
Partitioned tables and indexes are available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply