Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using SQL Servers Stored Procedures to return XML to Delphi Program

Posted on 2003-11-04
12
Medium Priority
?
1,081 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 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
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.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

927 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