Hi fellows,
I am a newbie, so new to these discussion groups. I had put this question in
Sqlserver.xml group. May be this question is related to ASP group. So,
putting it here. I am facing following problem:
I am passing a XML to SP, and SP in return send XML back. When I comment out
sp_xml_preparedocument (XML reading block) in SP, ASP is taking returning XML
as valid, but, if I uncomment input XML reading block, ASP is giving
following error:
--------------------------------------
Microsoft OLE DB Provider for SQL Server error '80040e21'
XML parsing error: Invalid at the top level of the document.
/../../MailListFixErrors.asp, line 124
--------------------------------------
I am using XP v2002 SP2, Sql-Server 2K Pro. Edition SP3 and using classic
ASP.
Here is my code snippet:
Store Proc:
-------------
CREATE PROC [dbo].[spValidateMList_MLST]
@XMLDoc text
AS
BEGIN
DECLARE @InputList TABLE (
[MailListName] [varchar] (40) ,
[guid] [varchar] (40) )
DECLARE
@MailListName [varchar] (40) ,
@CustomerID [varchar] (40) ,
@ShipperID [varchar] (40) ,
@ServerID [varchar] (40)
EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLDoc
SELECT @MailListName= MailListName,
@CustomerID= CustomerID,
@ShipperID= ShipperID,
@ServerID= ServerID
FROM OPENXML (@idoc, '/maillist',2)
WITH (maillistname [varchar] (40) ,
customerid [varchar] (40) ,
shipperid [varchar] (40) ,
serverid [varchar] (40) )
INSERT INTO @InputList
( MailListName, guid )
SELECT @MailListName, guid
FROM OPENXML (@idoc, '/maillist/row',2)
WITH (guid [varchar] (40) )
EXEC sp_xml_removedocument @idoc
......
......
SELECT
1 AS Tag,
NULL AS Parent,
NULL AS [Error!1!],
NULL AS [row!2!Code!element],
NULL AS [row!2!Message!element]
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL AS [Error!1!],
0 AS [row!2!Code!element],
(SELECT COUNT(*) AS ErrCount
FROM @MailListShipment
WHERE CheckedStatus in (1,2))
AS [row!2!Message!element]
FOR XML EXPLICIT
END
ASP code is as :
-------------------
Dim strExStoredProcedure
Const MSSQLXML_DIALECT = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
Const adExecuteStream = 1024
Const adWriteChar = 0
Dim conDB
Dim cmdXML
Dim stmXMLin
Dim stmXMLout
strExStoredProcedure= " [dbo].[spValidateMList_MLST] @XMLDoc = " &_
"'<maillist><maillistname>ml14</maillistname><customerid>ABC</customerid> "
&_
"<shipperid>ABC</shipperid><serverid>s400</serverid><row> " &_
"<guid>D3EA3AA2-49EC-4455-8D25-6D32BAD3D6DB</guid></row></maillist>' "
Set conDB = CreateObject("ADODB.Connection")
With conDB
.Provider = "SQLOLEDB"
.ConnectionString = connstr
.Open
End With
Set cmdXML = CreateObject("ADODB.Command")
Set cmdXML.ActiveConnection = conDB
Set stmXMLin = CreateObject("ADODB.Stream")
Set stmXMLout = CreateObject("ADODB.Stream")
With stmXMLin
.Open
.WriteText "<root xmlns:sql='urn:schemas-microsoft-com:xml-sql'>",
adWriteChar
.WriteText "<sql:query>", adWriteChar
.WriteText "EXEC " & strExStoredProcedure, adWriteChar
.WriteText "</sql:query></root>", adWriteChar
.Position = 0
End With
'Open Stream for output
stmXMLout.Open
With cmdXML
Set .CommandStream = stmXMLin
.Dialect = MSSQLXML_DIALECT
.Properties("Output Stream") = stmXMLout 'Output Stream
.Execute, ,adExecuteStream 'Execute ADO Command
End With
str=stmXMLout.ReadText 'get the XML as string
This SP runs fine in Query Analyzer and the returning XML seems to be OK.
But when we use â??Responseâ?? Object to get returning XML, and pass it back to
client side, it is working OK. But if I use "stmXMLout" Object Stream to get
returning XML, and want to process XML on server side code, it is giving me
the error I mentioned above.
Need help ASAP.
Thanks.