Solved

Using SQL Servers Stored Procedures to return XML to Delphi Program

Posted on 2003-11-04
12
1,050 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

756 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