henrikatwork
asked on
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/samplecod e on how to do this?
cheers,
henrik
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/samplecod
cheers,
henrik
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
>>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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Just tested it and it looks pretty! And yes, indeed it is quite painful. Thanks Anthony!
>>And yes, indeed it is quite painful.<<
I hope you meant: "it was quite painful" <g>
Anthony
I hope you meant: "it was quite painful" <g>
Anthony
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
Anthony
ASKER
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
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
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
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
ASKER
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
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
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
...
CREATE PROCEDURE dbo.x
@xml text
AS
...
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
Anthony
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