Solved

Using SQL Servers Stored Procedures to return XML to Delphi Program

Posted on 2003-11-04
12
1,040 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:henrikatwork
  • 5
  • 5
  • 2
12 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 9677257
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9679157
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
 
LVL 22

Accepted Solution

by:
CJ_S earned 500 total points
ID: 9679208
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9679384
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 9679450
Just tested it and it looks pretty! And yes, indeed it is quite painful. Thanks Anthony!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9679996
>>And yes, indeed it is quite painful.<<
I hope you meant: "it was quite painful" <g>

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9719578
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9720704
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 9720717
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
 
LVL 1

Author Comment

by:henrikatwork
ID: 9721081
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 9721109
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9722474
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question