Execute Stored Procedure with input xml and retrieving output xml

I created a stored procedure for MSSQL that returns a XML:
{code}
CREATE PROCEDURE [dbo].[SP_Processa_Dados]
    @XMLParamIN XML,
    @XMLParamOUT XML OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @XMLParamOUT= @XMLParamIN;
END
{code}

I want to use TADOStoredProc for input the Value the XML that and return that XML to give him treatment...
How can i do that?
0
Helder
6/11/2014 3:03:03 PM
embarcadero.delphi.ado 597 articles. 1 followers. Follow

6 Replies
1117 Views

Similar Articles

[PageSpeed] 22

"Helder Lopes" wrote in message news:680079@forums.embarcadero.com...
>I created a stored procedure for MSSQL that returns a XML:
> {code}
> CREATE PROCEDURE [dbo].[SP_Processa_Dados]
>    @XMLParamIN XML,
>    @XMLParamOUT XML OUTPUT
> AS
> BEGIN
>    SET NOCOUNT ON;
>    SET @XMLParamOUT= @XMLParamIN;
> END
> {code}
>
> I want to use TADOStoredProc for input the Value the XML that and return 
> that XML to give him treatment...
> How can i do that?


You will find the current ADO components do not recognize the XML type in 
SQL Server. Use varchar(max) for your parameters instead and make sure you 
are using the latest MS SQL drivers (native version 10 or 11) as they are 
needed to handle varchar(max).

If you want to use MS SQL XML methods inside the proc, you should be able to 
cast/convert to and from XML and varchar(max).

-- 
Wayne Niddery
"You know what they call alternative medicine that has been proven to work? 
Medicine." - Tim Minchin
0
Wayne
6/11/2014 11:01:05 PM
> {quote:title=Wayne Niddery wrote:}{quote}
> "Helder Lopes" wrote in message news:680079@forums.embarcadero.com...
> >I created a stored procedure for MSSQL that returns a XML:
> > {code}
> > CREATE PROCEDURE [dbo].[SP_Processa_Dados]
> >    @XMLParamIN XML,
> >    @XMLParamOUT XML OUTPUT
> > AS
> > BEGIN
> >    SET NOCOUNT ON;
> >    SET @XMLParamOUT= @XMLParamIN;
> > END
> > {code}
> >
> > I want to use TADOStoredProc for input the Value the XML that and return 
> > that XML to give him treatment...
> > How can i do that?
> 
> 
> You will find the current ADO components do not recognize the XML type in 
> SQL Server. Use varchar(max) for your parameters instead and make sure you 
> are using the latest MS SQL drivers (native version 10 or 11) as they are 
> needed to handle varchar(max).
> 
> If you want to use MS SQL XML methods inside the proc, you should be able to 
> cast/convert to and from XML and varchar(max).
> 
> -- 
> Wayne Niddery
> "You know what they call alternative medicine that has been proven to work? 
> Medicine." - Tim Minchin

another question:

Do you know any component for MSSQL connection that recognize this XML datatype?
0
Helder
6/12/2014 11:11:18 AM
Helder Lopes wrote:
> Do you know any component for MSSQL connection that recognize this XML datatype?

I'm not positive, but I suspect FireDAC does.
0
quinn
6/12/2014 8:02:01 PM
> {quote:title=Wayne Niddery wrote:}{quote}
>Use varchar(max) for your parameters instead and make sure you 
>are using the latest MS SQL drivers (native version 10 or 11) as they are 
> needed to handle varchar(max).
> 
> If you want to use MS SQL XML methods inside the proc, you should be able to 
> cast/convert to and from XML and varchar(max).
> 

Im using XE and still cant get the value from the stored procedure, even changing the INPUT/OUTPUT of the stored procedure for varchar(max).
Always get the same error:  "The Parameter object has not been properly defined. Provided is incomplete or inconsistent information."
Any alternate solution?
0
Helder
6/13/2014 8:42:15 AM
Helder Lopes wrote:
> Im using XE and still cant get the value from the stored procedure, even changing the INPUT/OUTPUT of the stored procedure for varchar(max).
> Always get the same error:  "The Parameter object has not been properly defined. Provided is incomplete or inconsistent information."
> Any alternate solution?

Use FireDAC. If you don't have XE6, get the trial and see if it works there.
0
quinn
6/16/2014 4:41:24 PM
XE6 solution only
0
Helder
6/23/2014 9:28:59 AM
Reply:

Similar Artilces:

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

Webservice
Hi, I have to develop a webservice client. The webservice requires the input in xml format and will return the output in xml format. I could not find any online resources on this specific topic. Any one know of any good article or tutorial? Thanks. http://forums.asp.net/906212/ShowPost.aspxhttp://vikasnetdev.blogspot.com Hi Vick, I think the post you referred talks about implementing a webservice (or am I wrong?) In my case, the webservice is already available from a third party. I have to create a webservice client to consume that webservice. It requires the input to be ...

XML in, XML out
Hi All (and a happy holiday to those that will get a break), I am trying to read in an XML file of addresses. I need to remove all the address data from the file where code =~ /^000/ (there are none in the example data below). I need to reproduce that data 'as is', so I need to honour the tag structure, although the order of the tags doesn't need to be honoured. I have been trying to use XML::Simple and I had a go with XML::Smart but I haven't been able to get the results I want. My best effort is below (with XML::Simple). There are a couple of differences ...

XMl to XML
Hello,   I retrieved an XML from the dataset which retrieves data from the table in the database. I need to present the data  in a different structure. Is XSLT the way or are there any other options. please let me know. Thanks!! Yes XSLT is a good choice to convert xml file to other forms. You can take a look at XSLT Tutorial.Sincerely,Young Fang...

Stored procedure which gives xml output..
hi, Am want to write procedure in sql server to give xml output.xml output should consist multiple nodes and attributes.can anyone give me sample procedures or give me a related link.                                      thanks...  Remember to click “Mark as Answer” on the post that helps you. Thank you!Always,Naveen..  http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=244&am...

FOR XML and OPENXML using Oracle / using xml in ORACLE stored procedure
Hi, I need a help with respect to xml operations in ORACLE.For example, Need to send multiple rows at once into stored procedure.orNeed to  pass data (a single entity) to stored procedure as a xml from Data Layer ( Instead of individual params)In case of sql server, There is an approach like,     From Front End,   Serialize the entity collection or single entity as xml and send to the db.   Pass xml to stored procedure from Data Layer     sql server stored proc in turn,  &n...

How to store XML into SQL with store procedure?
Hi all, I got this xml and I want to store it into SQL server (with SQLXML), I have a table with all the fields so I want to map the data in XML to all the fields in DB. - Is store procedure the best way to do it? Are there any better way to do it? - Do you have any similiar examples? - Any good articles on the net that related to this topic? Thanks a lot and happy new year! Edwin I am a newbie ----------- <quote symbolstring="QMCI" datatype="equity" delaymin="15" time="2003-09-12T14:43:05-0400"> <key> ...

Passing an XML variable argument to a SQL Server 2005 Stored Procedure with an XML data type
Is it possible to pass an XML variable to a SQL Server 2005 Stored Procedure with the XML data type as it's receiving argument? I can get the SQL Server Procedure to work perfectly with XML passed to it from another Stored Procedure, but as soon as I define a Stored Procedure from within a datawindow to pass parameters to it, I get "Cannot create DataWindow" and "XML parse error occurred on line number 1, near the XML text "0"". Essentially, I want to use the XML string, built by data keyed by the client within a datawindow, to be loaded to a temp t...

Is it possible to use a stored procedure that outputs xml to render a report? (SQL Server Reporting Service)
Is it possible to use a stored procedure that outputs xml to render a report? I mean, if my stored procedure outputs xml Can I catch fields from xml in my server report? If yes, can I have little bit idea about how to use?   Thanks in advance.....

Add XML declaration to XML Output
He, suppose I have a query that return XML ALTER PROC [dbo].[spEntreprise] AS SELECT Entreprise_ID , Nom , Numero , Rue , CodePostal , Ville , Contact , SIRET FROM Entreprise FOR XML AUTOwitch puts the results  <Entreprise Entreprise_ID="1" Nom="tocane Upd" Numero="1" Rue="7 avenue d" CodePostal="78300" Ville="POISSY Upd" Contact="contact1" SIRET="48709868" /> <Entreprise Entreprise_ID="2" Nom="tocus " Numero=&qu...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

xml with stored procedure
  From asp.net data entry page, create a xml file from data entered by user and Pass that xml to stored procedure and insert into a relational table. Don't store xml as is in db, map data to columns of user fields and insert them like normal table When insert is completed a button should be shown to user. when clicked create xml out of that table data, pass it back to asp.net page and read xml into various labels as entered by user. g.k.chaitanya Hi sunnysc,Does this your homework ? Have you done research yourself ? Hi sunnysc , Here is one good article about ...

XML::Parser XML::DOM XML::XSLT
------_=_NextPart_001_01C6E0D0.3E27D77A Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Hi Craig and fellow VMS Perlers, You last wrote about this Fri 3/16/2001 11:24 AM, not counting your very helpful comments on Eisner/Decuserve more recently. One of my colleagues sent me an email requesting that I make XML::XSLT=20 available on VMS. perl 5.8.6, VMS 7.3-2, DECC 7.1 latest ACRTL (version 3) on Alpha XML::XSLT (version 0.48) depends on XML::Parser (version 2.34). This depends on expat (version 2.0.0). I built expat a...

Inserting XML encoding specification to XML output
Hi! We would like to insert the XML encoding specification (<?xml version="1.0" encoding="iso8859-1"?>) to the first line of an XML file output (XML file from the Delimited Text Driver). Anyone done this? Regards, Tor Harald Lothe -- thlo ------------------------------------------------------------------------ There is more to it than just inserting the encoding specification, the file actually has to be encoded that way. I believe there is a driver parameter that allows you to specify the encoding which should do both for you. http:...

Web resources about - Execute Stored Procedure with input xml and retrieving output xml - embarcadero.delphi.ado

Mother of three killed landlord after he tried to stop her retrieving her son's shirt
A mother of three has been found guilty of killing her landlord after he tried to stop her retrieving a red polo shirt her son needed for school ...

Retrieving bodies a 'horrifying' experience (01:43)
Anti-jump measures on the Story Bridge should be "fast tracked" and water police funding increased so CityCats don't have to pick up bodies. ...

Man drowns retrieving toy boat
A man has drowned after entering the Wimmera River at Horsham to retrieve a remote-controlled boat.

Bishop says there is no time limit on retrieving MH17 remains
THE Australian Federal Police have warned that it is too dangerous to approach the MH17 crash site in war-torn eastern Ukraine until security ...

Search underway for man swept away in Brisbane River while retrieving toy boat
A search is underway for a man swept away in the Brisbane River while retrieving a toy boat.

DPRK blames US for suspension of retrieving American soldiers' remains
DPRK blames US for suspension of retrieving American soldiers' remains People's Daily Online PYONGYANG, Oct. 13 The Democratic People's Republic ...

Father killed retrieving Christmas gifts from broken-down car
Richard Perrin, 57, was struck and killed by another driver early Saturday along Hwy 170 in Roanoke.

Yankee retrieving a big Canada
Yankee retrieving a big Canada

iPhone dev: Retrieving user phone numbers
... a secret to recovering user phone numbers that does not involve the … One of the most common questions I get these days has to do with retrieving ...

Girl stuck in Dover storm drain retrieving phone
A teenage girl gets stuck in a storm drain in Dover while trying to retrieve her mobile phone which had fallen into it.

Resources last updated: 1/17/2016 12:20:02 PM