Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
' "SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
' "Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
' "SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
' "RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
' "WHERE asset_cd='" & AssetID & "'" & _
' "AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 & "<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 & "<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 & "<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!

Error parsing XML SQL Command

Hi,
I'm getting some strange behavior when calling a stored proc...
It's an invoice page, so it has a list of assets, description, and $
total
You can modify this information and click on Update to update the
records with entered information. This works fine if I have one asset
in the invoice. But if I have more than one asset I get the following
error...
ParseXMLCommand->ReportEngine.Retrieval.ParseXMLCommand() [v
1.1.3]->ReportEngine.Retrieval.ExecuteQuery() [v 1.1.3] error
'80040067'
Error parsing XML SQL Command (Only one top level element is allowed in
an XML document. ).
/common/jax/applications/meter_invoices_client2.asp, line 126
Below is the code from meter_invoices_client2.asp which takes care of
this SQL operation. The commented lines are the existing query, which
is now being moved to a stored proc. with the code below. The embedded
SQL works fine under any circumstance. I don't think its a problem
with the stored procedure as it doesnt even get there yet.
'BEConn.Execute "Update InvoiceAsset SET SortTotal='" &
replace(Request.Form("SortTotal" & AssetID),"'","''") & "', " & _
'"SortOrderID='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "', " & _
'"Asset_location='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "', " & _
'"SubTotalDesc='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") & "',
" & _
'"RecordChanged='" & trim(Request.Form("Perm")) & "' " & _
'"WHERE asset_cd='" & AssetID & "'" & _
'"AND invoicenumber ='" & invoicenumber & "';"
XML_SP5 = XML_SP5 & "<sp name='p_update_sortOrders' db-code='" &
dbId & "' loc-code='1'>"
XML_SP5 = XML_SP5 &"<param name='@.asset_total' datatype='string'
index='1' value='" & replace(Request.Form("SortTotal" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_orderId' datatype='string'
index='2' value='" & replace(Request.Form("SortOrderID" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_location' datatype='string'
index='3' value='" & replace( Request.Form("Asset_Location" &
AssetID),"'","''") & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.asset_subtotal' datatype='string'
index='4' value='" & replace(Request.Form("SubTotalDesc" &
replace(Request.Form("SortTotal" & AssetID),"'","''")),"'","''") &
"'/>"
XML_SP5 = XML_SP5 &"<param name='@.perm' datatype='string' index='5'
value='" & trim(Request.Form("Perm")) & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.assetID' datatype='string'
index='6' value='" & AssetID & "'/>"
XML_SP5 = XML_SP5 &"<param name='@.invoiceNo' datatype='string'
index='7' value='" & invoicenumber & "'/>"
XML_SP5 = XML_SP5 & "</sp>"
Set oXMLRetrieve = Server.Createobject("ReportEngine.Retrieval")
Call oXMLRetrieve.ExecuteQuery(reRecordSet,XML_SP5)
I think posting on here just helps me out...lol
I got it to work finally...if anyone is interested...the error was
occurring because it was looping through all the fields on the form to
check if it is the one to be updated...so everytime through the loop it
was invoking the query XML_SP5 but XML_SP5 was continually being
appended do so it would look like this on the 2nd iteration...
<sp name='p_update_sortOrders' db-code='2' loc-code='1'><param
name='@.asset_total' datatype='string' index='1' value='1029'/><param
name='@.asset_orderId' datatype='string' index='2' value='2'/><param
name='@.asset_location' datatype='string' index='3' value='WEXFORD, PA
900-18999'/><param name='@.asset_subtotal' datatype='string' index='4'
value=''/><param name='@.perm' datatype='string' index='5'
value='N'/><param name='@.assetID' datatype='string' index='6'
value='663018'/><param name='@.invoiceNo' datatype='string' index='7'
value='3459956'/></sp><sp name='p_update_sortOrders' db-code='2'
loc-code='1'><param name='@.asset_total' datatype='string' index='1'
value='4016'/><param name='@.asset_orderId' datatype='string' index='2'
value='43'/><param name='@.asset_location' datatype='string' index='3'
value='COLUMBIA, MD 900-19002'/><param name='@.asset_subtotal'
datatype='string' index='4' value='SPEED CODE 4016'/><param
name='@.perm' datatype='string' index='5' value='N'/><param
name='@.assetID' datatype='string' index='6' value='663021'/><param
name='@.invoiceNo' datatype='string' index='7' value='3459956'/></sp>
which makes it 2 top level elements (sp) and hence throws the error
Cheers!
sql

error message: The text, ntext, or image data type cannot be selected as DISTINC

I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird--xml is not complete(cut in the middle).

I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

ThanksQuery Analyzer will not return complete fields beyond a certain length (even in "results in text" mode). The only way I know of to overcome this limitation is to:

Declare @.Foo varchar(8000)

SELECT @.Foo = TextColumn FROM MyTable

PRINT @.Foo

You can also use VB, VBScript or VBA and use the Debug.Print functionality (or write the results to a file).

Regards,

Hugh Scott

Originally posted by heidil90
I want to use For XML Explicit to get well-formatted XML from database. Everytime if I tried to select something in DB whose datatype is text, error message came out: The text, ntext, or image data type cannot be selected as DISTINCT.

I tried to change data type from text to varchar and set size to 8000, but result in query analyzer is weird--xml is not complete(cut in the middle).

I wonder if anyone here could help me out with this problem. Your reply will be highly appreciated!

Thanks|||Thanks so much for helping me! Yesterday I figured out how to show the complete XML in Microsoft Internet Explore by running the utility configure SQL server XML support in IIS.

I am still wondering why I use CDATA directive in my FOR XML EXPLICIT for a field that has a text datatype can work in the first level of hierarchy, but not in the second or third level of hierarchy. Do you have any idea? Thanks in advance!

OK version:
select 1 as tag,
null as parent,
Employees.EmployeeID as [Employee!1!EmployeeID],
Employees.Notes as [Employee!1!!CDATA]
from Employees

for xml explicit
**************
Query batch completed.

=================================
==================================
wrong version:

select 1 as tag,
null as parent,
Employees.EmployeeID as [Employee!1!EmployeeID],
null as [Employee!2!!CDATA]
from Employees

union

select 2,1,
null,
Employees.Notes

from Employees
for xml explicit
*********************
Server: Msg 8163, Level 16, State 4, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.