XML document could not be created because server m

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

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

XML document could not be created because server m

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2013-10-31 : 06:29:09

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

545 Posts

Posted - 2013-10-31 : 08:09:49


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

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-10-31 : 08:16:54

[code]
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)

[/code]

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources