Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 XML document could not be created because server m
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimoomba
Yak Posting Veteran

India
90 Posts

Posted - 10/31/2013 :  06:29:09  Show Profile  Reply with Quote
Hi All,

Iam getting the following issue when i ran the below query in my sql server 2008 r2 database :

DECLARE @hdoc 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>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
EXEC sp_xml_removedocument @hdoc

Issue: "XML document could not be created because server memory is low.
Use sp_xml_removedocument to release XML documents.
Could not find prepared statement with handle 0.
sp_xml_removedocument: The value supplied for parameter number 1 is invalid."

Kindly help me on the same

rams

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 10/31/2013 :  08:09:49  Show Profile  Reply with Quote
hi


set @xmlDoc=cast(@doc as xml)
select 
	Customer.u.value('@CustomerID[1]','varchar(50)') as CustomerID
	,Customer.u.value('@ContactName[1]','varchar(50)') as ContactName
	,O.x.value('@EmployeeID[1]','int') as EmployeeID
	,O.x.value('@OrderDate[1]','Date') as OrderDate
	,Od.w.value('@OrderID[1]','int') as OrderID
	,Od.w.value('@ProductID[1]','int') as ProductID
	,Od.w.value('@Quantity[1]','int') as Quantity
from
	    @xmlDoc.nodes('ROOT/Customer') as Customer(u)
		outer apply Customer.u.nodes('./Order') as O(x)
		outer apply O.x.nodes('./OrderDetail') as Od(w)




1- is missing OpenXML
2- maybe varchar(1000) is not enough try varchar(MAX)



Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

stepson
Aged Yak Warrior

Romania
545 Posts

Posted - 10/31/2013 :  08:16:54  Show Profile  Reply with Quote

select 

	 Od.u.value('../../@CustomerID[1]','varchar(50)') as CustomerID
	,Od.u.value('../../@ContactName[1]','varchar(50)') as ContactName
	,Od.u.value('../@EmployeeID[1]','int') as EmployeeID
	,Od.u.value('../@OrderDate[1]','Date') as OrderDate
	,Od.u.value('@OrderID[1]','int') as OrderID
	,Od.u.value('@ProductID[1]','int') as ProductID
	,Od.u.value('@Quantity[1]','int') as Quantity
from
	    @xmlDoc.nodes('ROOT/Customer/Order/OrderDetail') as Od(u)





Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000