Hi

I want to open up an xml document from a url and pass it as a
parameter to a stored proc to insert into a db.

<%
Response.Buffer = True
Dim objXMLHTTP, xml, xmlHeadline,oConn, oPath, strSQL

' Create an xmlhttp object:
Set xml = Server.CreateObject("MSXML2.ServerXMLHTTP")

' Opens the connection to the remote server.
xml.Open "POST", "http://somesite.asp?somexml.xml", False
' Actually Sends the request and returns the data:
xml.Send

xmlHeadline = xml.responseXML.xml
' Remove invalid copyright symbol
xmlHeadline= replace(xmlHeadline,chr(169),"")
' Open connection
Set oConn = Server.CreateObject("ADODB.Connection")
oPath="Provider=sqloledb;Data Source=mydatabase;Initial
Catalog=MyCat;Integrated Security=SSPI"
strSQL = "sp_insertHeadlines '" & xmlHeadline & "'"
oConn.open oPath,strSQL
' Check to see if the xml is displayed ok
Response.Write xmlHeadline
Set xml = Nothing
%>

The xml is displayed fine in the browser thanks to response.write, so
its a valid document.

The sp I wrote is:

CREATE PROCEDURE dbo.sp_insertheadlines
(

@xmlHeadline text
)

AS

DECLARE @idoc int

--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlHeadline
-- Execute a SELECT statement that uses the OPENXML rowset provider
Insert Into NewsData
SELECT *
FROM OPENXML (@idoc, '/cXmlHeadlineResults/Headline',2)
WITH (
StoryId varchar(80),
StoryUrl varchar(48),
HeadlineText varchar(1000),
BriefText varchar (1000),
ProviderName varchar(200),
ItemTime varchar (50)
)
where StoryId not in (select StoryId from NewsData)
-- Clear the XML document from memory
EXEC sp_xml_removedocument @idoc
GO


This works fine if I set the xml document in the sp, but not if I pass
it as a parameter through asp.

Can someone tell me (slowly!) what I'm doing wrong?!

Thanks