XML From SQL2000 (Probs with FOR XML EXPLICIT) 
Author Message
 XML From SQL2000 (Probs with FOR XML EXPLICIT)

Hi There

I am trying to retrieve an XML string from a COM function that calls a
Stored Procedure using the FOR XML EXPLICIT statement.
I am getting the following:
<?MSSQLError HResult="0x80040e14" Source="Microsoft OLE DB Provider for SQL
Server" Description=""?>

The stored procedure runs great if a run it by itself so that is not the
problem. It seems to be something with the FOR XML EXPLICIT statement as
when I run a stored procedure using the FOR XML AUTO it all seems fine.

Anybody unfderstand this?

Below is the function  and sp
*********************************
Public Function GetXML() As String
    Dim conn As ADODB.Connection
    Dim str As ADODB.Stream
    Set conn = New ADODB.Connection
    conn.Open strConn
    Set str = New ADODB.Stream
    str.Mode = adModeRead
    str.Open
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandText = "sp_Members_GetXML"
    cmd.CommandType = adCmdStoredProc
    cmd.Properties("Output Stream").Value = str
    cmd.Execute , , adExecuteStream
    GetXML = str.ReadText(-1)
End Function

*********************************
Stored Procedure:
Create spMembers_GetXML AS
SELECT  1 AS TAG,
 NULL AS Parent,
 Members.MemberID AS [Member!1!MemberID],
 Members.Firstname AS [Member!1!Firstname],
 Members.Brief AS [Member!1!!CDATA],
 NULL AS [MemberAddress!2!Street1]
FROM Members

UNION ALL

SELECT  2,
 1,
 Members.MemberID,
 Members.Firstname,
 Members.Brief,
 MemberAddresses.Street1
FROM Members
JOIN MemberAddresses
ON Members.MemberID = MemberAddresses.MemberID
ORDER BY [Member!1!MemberID]
FOR XML EXPLICIT



Sat, 20 Dec 2003 00:03:56 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Using SQL2000, returning an XML document (string) instead of a recordset, from a stored procedure

2. SQL2000, XML, ADO and SOAP

3. XML document from SQL2000

4. Problems with retrieving Data with FOR XML EXPLICIT

5. xml explicit

6. ADO 2.5 and 2.7 using For XML EXPLICIT causes problems in the results

7. Stored Procedures returning data with FOR XML EXPLICIT

8. having probs with my xml html parser thingy....

9. XML, Converting Word doc's to XML files (O'2000)

10. How to create a secure connection, send an xml msg and receive and xml response

11. Render XML+XSL into PrintDocument (not XML) ?

12. XML Newbie question, getting a value from an XML string

 

 
Powered by phpBB® Forum Software