Parsing of XML String to then execute Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Parsing of XML String to then execute Proc

Author

Topic

NickyJ
Starting Member

46 Posts

Posted - 2005-08-10 : 11:04:32

Hi all,

Right here's the situ :

I am being passed an XML string from a Flash application looking like :

<list CntryId="12" JobType="0" ></list>

These values need to be used to query the SQL database, now am I right in saying what I need to do is parse the above string using OPENXML, then to use these values in a Stored Procedure which basically queries the database and uses the FOR AUTO XML statement to output the records back as XML.

Question is : Is this the only way ?? Best way

The above Stored Proc will be called via ASP through ADO.
Thanks all

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-11 : 04:46:47

Sounds perfectly reasonable to me. Clearly it's no bother to write the OPENXML statement to shred the fragment you're getting from the app. How complex is the XML it's expecting back?

Mark

NickyJ
Starting Member

46 Posts

Posted - 2005-08-11 : 05:03:53

No complexity in the XML being returned, am a little surpirsed no-one else has done this as yet, is it not a common task to be doing ???

thanks

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-11 : 05:20:21

In my experience, yeah. Given the simplicity of the input XML, you may be better off extracting the two parameters in the ASP page and passing them to the stored proc this way. This saves the overhead of creating a second dom object from within SQL Server.

Mark

NickyJ
Starting Member

46 Posts

Posted - 2005-08-11 : 05:59:15

Hit a problem, the ASP page calls the stored procedure which has an output parameter, proc as below :

CREATE PROCEDURE pr_GetJobInfo2
@JobType int,
@CntryID int,
@JobTitle varchar(8000) OUTPUT
AS
SELECT @JobTitle = ltrim(rtrim(JobTitle))
FROM dbo.JOBS
WHERE JobType = @JobType
AND CntryID = @CntryID
FOR XML AUTO
GO

Now all is fine if I want it to return just the JobTitle but I want it to return XML and when I add for XML auto I get the following message as I see an output parameter cannot be XML, any ideas ??? Where have I gone wrong ??

Error Message
Microsoft OLE DB Provider for SQL Server error '80040e14'

The FOR XML clause is not allowed in a ASSIGNMENT statement.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-11 : 06:11:22

Hi Nicky
Just return a resultset

Mark

NickyJ
Starting Member

46 Posts

Posted - 2005-08-11 : 07:32:33

Hi Mark,

Am new to all this so not sure how to do that ??

My code's like the following :

<% dim cn
dim cmd
dim p
dim pJobID
dim pJobTitle
dim retvalue
dim rs

cn = "Provider=SQLOLEDB.1;Data Source=lon-nt-sq02; Initial Catalog=hncom; user id = 'sa';password='contacts01'"

Set cmd = Server.CreateObject ("ADODB.Command")

cmd.ActiveConnection = cn
cmd.CommandText = "pr_GetJobInfo2"
cmd.CommandType = adCmdStoredProc

set p = cmd.Parameters
p.Append cmd.CreateParameter("JobType", adInteger, adParamInput)
p.Append cmd.CreateParameter("CntryID", adInteger, adParamInput)
p.Append cmd.CreateParameter("JobTitle", adVarChar, adParamOutput,500)

cmd("JobType")= 1
cmd("CntryID")= 6

Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open cmd

Response.write rs.Fields.Item("XML_F52E2B61-18A1-11d1-B105-00805F49916B").Value

' clean up
If rs.State = adStateOpen then
rs.Close
End If
If cmd.State = adStateOpen then
cmd.Close
End If

Set rs = Nothing
Set cmd = Nothing

%>

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-11 : 10:55:07

OK - that approach is not going to work.

Unfortunately, you can't access the XML document returned from your query using an ADO recordset. Instead, you need to stream out the XML data into an ADO Stream. This requires that you set up your data access objects a little differently.

Here's an idea of what you'll need to do:

	Set objADOStream = CreateObject("ADODB.Stream")
	
	objADOStream.Open
	objADOCmd.Properties("Output Stream") = objADOStream
	objADOCmd.Execute , , 1024
	strXML = objADOStream.ReadText

Also, I'd strongly recommend against using the sa account for this (and handing out your sa password)!

Mark

NickyJ
Starting Member

46 Posts

Posted - 2005-08-11 : 11:23:01

Am looking into your suggestion now, ADO.streams new to me!

Current sa password is on development box but agree before using in production will use alternative login with limited permission!

Do you know any decent samples of ADO.stream code ??

Thanks again

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-12 : 09:41:23

Try this:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscresultsetstreams.asp[/url]

Mark

NickyJ
Starting Member

46 Posts

Posted - 2005-08-12 : 12:30:11

Thanks for the links Mark, interesting reading!!

Now have code as below but not working and bashing head against wall wondering why ??? Anyone



<%
dim objStream
dim objConn
dim objComm
dim objXML

set objStream = Server.CreateObject("ADODB.Stream")
set objConn = Server.CreateObject("ADODB.Connection")
set objComm = Server.CreateObject("ADODB.Command")
set objXML = Server.CreateObject("MSXML2.DOMDocument")

objConn.Open "Provider=SQLOLEDB; Data Source=LON-NT-SQ02; Initial Catalog=myProducts; UId=sa; Pwd=password"

objComm.ActiveConnection = objConn
objComm.CommandType = adCmdStoredProc
objComm.CommandText = "sp_GetExplicitXML"

objStream.Open
objComm.Properties("Output Stream").Value = objStream

objComm.Execute ,, adExecuteStream
objStream.Position = 0

objXML.LoadXML("<?xml version='1.0'?><?xml-stylesheet type='text/xsl'
href='prodtest.xsl'?><My_Products>" &
objStream.ReadText & "</My_Products>")

if objXML.parseError.errorCode <> 0 then
Response.Write "Error loading XML: " & objXML.parseError.reason
Response.End
end if

Response.ContentType = "text/xml"
Response.Write objXML.xml
%>

What's wrong ???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources