Re: SP input XML and Return XML
- From: "AsadCheema" <asad_cheema@xxxxxxxxxxx>
- Date: Wed, 22 Jun 2005 13:05:02 -0700
Hi Rys!
Thanks for the prompt response. The SP was working fine, but the error i was
getting was on ASP side. If i comment out sp_xml_preparedocument (XML reading
block) from my SP, the ASP didn't get error, otherwise it is retuning 'XML
Parsing...' error. Also i want to mention, that if i get back the XML in ASP
Response object, and navigate it in Client side, it was working OK, but as if
I get the returning XML in 'stmXMLout' object as follows
With cmdXML
Set .CommandStream = stmXMLin
.Dialect = MSSQLXML_DIALECT
.Properties("Output Stream") = stmXMLout 'Output Stream
.Execute, ,adExecuteStream 'Execute ADO Command
End With
, it is giving me error. Thanks.
Asad.
"Michael Rys [MSFT]" wrote:
> The error indicates that your XML is not well formed and the parser
> complains about it.
>
> However, I had some problems with your stored proc, so I fixed it in the
> following way and executed it in the query analyzer (I used SQL Server
> 2005). And it worked:
>
> DROP PROC [dbo].[spValidateMList_MLST]
>
> go
>
> CREATE PROC [dbo].[spValidateMList_MLST]
>
> @XMLDoc text
>
> AS
>
> BEGIN
>
> DECLARE @idoc int;
>
> DECLARE @MailListName [varchar] (40);
>
> DECLARE @CustomerID [varchar] (40);
>
> DECLARE @ShipperID [varchar] (40);
>
> DECLARE @ServerID [varchar] (40);
>
> DECLARE @InputList TABLE(MailListName varchar(40), guid 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) 'maillistname',
>
> CustomerID [varchar] (40) 'customerid',
>
> ShipperID [varchar] (40) 'shipperid',
>
> ServerID [varchar] (40) 'serverid')
>
>
> -- 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 @InputList
>
> -- WHERE CheckedStatus in (1,2)
>
> )
>
> AS [row!2!Message!element]
>
> FOR XML EXPLICIT
>
> END
>
> go
>
> exec[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>'
>
> Could you please provide me with the T-SQL statements that I can run to
> repro the error?
>
> Thanks
>
> Michael
>
>
>
> PS: I did the renaming in the WITH clause since I normally run with a
> case-sensitive collation. your mixing of lower and uppercase would work in a
> case-insensitive environment...
>
> "AsadCheema" <asad_cheema@xxxxxxxxxxx> wrote in message
> news:12E2BBEE-0730-4714-95E0-C38FEBB70131@xxxxxxxxxxxxxxxx
> > Hi fellows,
> > I am a newbi, so new to these discussion groups. I am facing following
> > poblem:
> > Passing a XML to SP, and SP also is returning XML. When i commentout
> > sp_xml_preparedocument block, ASP is taking returning XML, but as 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 2000 v8.00.760 and using classic ASP.
> > Here is my code:
> > Store Proc:
> > -------------
> > CREATE PROC [dbo].[spValidateMList_MLST]
> > @XMLDoc text
> > AS
> > BEGIN
> > 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 code was runing fine when we were not taking returning "stmXMLout" as
> > Output Stream, instead we were using "Response" and returning result to
> > Client side.
> > Need help ASAP, cuz. lot thing depend on this.
> > Thanks.
>
>
>
.
- Follow-Ups:
- Re: SP input XML and Return XML
- From: Bertan ARI [MSFT]
- Re: SP input XML and Return XML
- References:
- SP input XML and Return XML
- From: AsadCheema
- Re: SP input XML and Return XML
- From: Michael Rys [MSFT]
- SP input XML and Return XML
- Prev by Date: Re: CDATA & FOR XML PATH
- Next by Date: Re: Date time conversion & language
- Previous by thread: Re: SP input XML and Return XML
- Next by thread: Re: SP input XML and Return XML
- Index(es):
Relevant Pages
|
