Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using SQL Servers Stored Procedures to return XML to Delphi Program

Posted on 2003-11-04
12
Medium Priority
?
1,075 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 1500 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

715 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