Using SQL Servers Stored Procedures to return XML to Delphi Program

Hi all,

I have to write a Database Access Layer in Delphi, that calls Stored Procedures on SQL Server 2000. The Stored Procedures should return XML and, if possible, take XML as parameter.

Can anyone give me hints/tips/links/samplecode on how to do this?

cheers,

henrik

LVL 1
henrikatworkAsked:
Who is Participating?
 
CJ_SCommented:
Oh really? I thought I tried 'm about all. I tried XML using several ways and eventually hacked around it by using the following bit of code... :

EXEC(
'DECLARE @Handle int
 EXEC sp_xml_preparedocument @Handle OUT, ''' +
 @xml0 + @xml1 + @xml2 + @xml3 + '''
 DECLARE he_cur CURSOR FOR SELECT @Handle'
)
OPEN he_cur
FETCH he_cur INTO @xml_he
DEALLOCATE he_cur

-- maak ordercursor
declare ordercur CURSOR FOR
SELECT    *
FROM       OPENXML (@xml_
...

0
 
CJ_SCommented:
Sample selection using XML:
select * from TBLTip for xml auto

Sample update using XML:

Please note that XML within SQL can only be 8000 characters.

CREATE PROCEDURE dbo.setxml
@xml varchar(8000)
AS

declare @xml_he int
EXEC sp_xml_preparedocument @xml_he OUTPUT, @xml
SELECT    *
FROM       OPENXML (@xml_he, '/ROOT/XMLNODENAME',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

EXEC sp_xml_removedocument @xml_he


0
 
Anthony PerkinsCommented:
CJ_S,

>>Please note that XML within SQL can only be 8000 characters<<
Actually you can use a parameter of data type text, which does not have that limitation.

Anthony

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
Ouch, that looks painful <g>

But yes, we use text parameters all the time here and they work fine.  I suspect they may be slower than varchar, but I have never benchmarked any comparison, as it did not really apply.

Anthony
0
 
CJ_SCommented:
Just tested it and it looks pretty! And yes, indeed it is quite painful. Thanks Anthony!
0
 
Anthony PerkinsCommented:
>>And yes, indeed it is quite painful.<<
I hope you meant: "it was quite painful" <g>

Anthony
0
 
Anthony PerkinsCommented:
I honestly don't care about the points, but I truly hope you are not going to use dynamic SQL for something that can be easily accomplished without.  Please understand that dynamic SQL implies bad performance and lousy security and as such should only be used as a last resort.

Anthony
0
 
henrikatworkAuthor Commented:
Hmm... how come? I thought it would be a much better architecture to use XML throughout the whole architecture, or???

You really scares me, please tell me why using Stored procedures is so bad? I thought it would be much faster then SQL Statements?

cheers,

henrik
0
 
CJ_SCommented:
henrikatwork,
XML is good enough, but the answer you accepted was using dynamic sql. With other words, the answer accepted contains a string which is then executed. Instead you should have accepted my first comment or use acperkins idea to use a text field for the XML.

CJ
0
 
henrikatworkAuthor Commented:
Oh, thanks for the comment.

One last question (to see if I'm checking this)

Always when I have XML Strings smaller then 8000 characters, I use SQL, if not, I use the dynamic SQL?

cheers,

henrik
0
 
CJ_SCommented:
When you can pass an xml string which exceeds 8000 characters you should use a text datatype instead of varchar(8000).

CREATE PROCEDURE dbo.x
@xml text
AS
...
0
 
Anthony PerkinsCommented:
CJ_S comments are exactly right.  My problem is not with Stored Procedures and XML, we use this combination all the time, my problem has more to do with Dynamic SQL (any time you use EXEC('SQL statement goes here'), especially when there is no need)

Anthony
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.