SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimoomba
Yak Posting Veteran

India
89 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
Constraint Violating Yak Guru

Romania
403 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
Constraint Violating Yak Guru

Romania
403 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000