Passing complex parameters

Deepak-
"Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
(1) Permissions (The data provider is probably using integrated security)
(2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
(3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
(4) Set IgnoreNamespaces="true" in the query
Hope this helps.
Jon Heide
SQL Server Reporting Services
--Original Message--
From: Deepak@.discussions.microsoft.com
Posted At: Wednesday, October 05, 2005 2:06 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Passing complex parameters
Subject: RE: Passing complex parameters
Hi Wayne,
I was at PASS last week as well, and saw those demos. This SQL Server 2005
Books Online entry discusses XML data sources for Reporting Services, but I
can't get the example at the end (which simply lists reports from the Report
Server) to work. The dataset query returns an error like "Failed to execute
web request for the specified URL". Maybe Brian Welcker or Bruce L-C can
comment/help?
- Deepak
http://msdn2.microsoft.com/en-us/library/ms228288
>>
SQL Server 2005 Books Online
Retrieving XML Data
XML documents are a supported data source type for reports that you build
and run in SQL Server 2005 Reporting Services. An XML data processing
extension is provided to retrieve and process the data.
...
Example: Retrieving XML Data from a Web service
To build the query, use the generic query designer. Use the following steps
to learn how to specify the connection and return XML data from a Web
service. In this example, the dataset returns items from the report server
database.
...
>>
"Wayne Snyder" wrote:
> I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
>
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
>
> Then in the query for the data source, they did an XML query, passing in the
> SOAP query, which was copied from the web services description...It worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
>
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
>
> "Brian Smith" wrote:
>
> > Is it possible to pass a .net DataSet to a Report, and have that used as
> > data within the report?
> > I have a dynamically generated list of items in a .net DataSet that I
> > need to pass to a top-level report which then calls subreports for each
> > item.
> > I'm not sure how you can process such a parameter in the report -
> > obviously its just an xml string, but how can it be re-rendered to data.
> > Alternatively I could pass a comma separated string, but the same
> > question arises. An example would be great (he said hopefully).
> >
> > Also, is there a limit to the size of a string parameter?
> >
> > brian smith
> >Jon,
Thanks for those tips. I've got a SharePoint Lists Web Service (using the
"GetListCollection" method without parameters) source working, but still
can't get results from other methods with parameters. Next I'm going to try
this sample "Report Manager" report from Brian Welcker's blog, which should
be similar to the SQL Server 2005 BOL XML sample:
http://home.ripway.com/2005-6/332074/ReportManager.rdl
"Jon Heide" wrote:
> Deepak-
> "Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
> (1) Permissions (The data provider is probably using integrated security)
> (2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
> (3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
> (4) Set IgnoreNamespaces="true" in the query
> Hope this helps.
> Jon Heide
> SQL Server Reporting Services
>
> --Original Message--
> From: Deepak@.discussions.microsoft.com
> Posted At: Wednesday, October 05, 2005 2:06 PM
> Posted To: microsoft.public.sqlserver.reportingsvcs
> Conversation: Passing complex parameters
> Subject: RE: Passing complex parameters
>
> Hi Wayne,
> I was at PASS last week as well, and saw those demos. This SQL Server 2005
> Books Online entry discusses XML data sources for Reporting Services, but I
> can't get the example at the end (which simply lists reports from the Report
> Server) to work. The dataset query returns an error like "Failed to execute
> web request for the specified URL". Maybe Brian Welcker or Bruce L-C can
> comment/help?
> - Deepak
>
> http://msdn2.microsoft.com/en-us/library/ms228288
> >>
> SQL Server 2005 Books Online
> Retrieving XML Data
> XML documents are a supported data source type for reports that you build
> and run in SQL Server 2005 Reporting Services. An XML data processing
> extension is provided to retrieve and process the data.
> ...
> Example: Retrieving XML Data from a Web service
> To build the query, use the generic query designer. Use the following steps
> to learn how to specify the connection and return XML data from a Web
> service. In this example, the dataset returns items from the report server
> database.
> ...
> >>
>
> "Wayne Snyder" wrote:
> > I saw someone doing something similar at the PASS conference last week...
> > I'll tell you what I remember and hope it helps... They were using a Web
> > Service as a data source.
> >
> > They chose XML as the data provider, then in the connect string pointed to
> > the Web service.. asmx
> >
> > Then in the query for the data source, they did an XML query, passing in the
> > SOAP query, which was copied from the web services description...It worked
> > for him, but I suspect it would take me a while to duplicate what he had
> > done...
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "Brian Smith" wrote:
> >
> > > Is it possible to pass a .net DataSet to a Report, and have that used as
> > > data within the report?
> > > I have a dynamically generated list of items in a .net DataSet that I
> > > need to pass to a top-level report which then calls subreports for each
> > > item.
> > > I'm not sure how you can process such a parameter in the report -
> > > obviously its just an xml string, but how can it be re-rendered to data.
> > > Alternatively I could pass a comma separated string, but the same
> > > question arises. An example would be great (he said hopefully).
> > >
> > > Also, is there a limit to the size of a string parameter?
> > >
> > > brian smith
> > >
>

Pass XML string as parameter for report data source?

Hello,
Is it possible to pass an XML document as a string parameter to a Reporting
Services 2005 report and use this report as a data source for the report?
I know you can source from a web service, the problem is that the effort
needed to convert what I have into a web service will be more than we would
like to take on. Instead, I am trying to pass an XML structure as a string
parameter to a report and then setting the Command Text like so:
<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
TIA!!!I figure out how to do what I am looking to do.
1. Create a new report and specify a sample XML file as your data source.
This way you get all the DataSet <Field> elements you need automatically.
2. Create a new blank report (without the wizard using Add > New Item >
Report).
3. Create a string report parameter to hold your XML data structure for the
new report. I give it a default value of my sample XML used to create the
report in step 1 for testing purposes.
4. Create a new DataSet (Data tab > <New DataSet...> from DataSet dropdown).
5. Name the DataSet (like "XmlSource").
6. Select XML from the Type dropdown.
7. Specify your connection string like so (use the appropriate parameter):
="<Query><XmlSource>" & Parameters!XmlSource.Value & "</XmlSource></Query>"
8. Go to your report you created in Step 1 in "Code View" and copy the
<Fields> element from your <DataSet> element. Now paste this <Fields>
element into the new report's <DataSet> element.
9. Using the fields, drag and drop to complete the layour of your report.
This is sort of a hack to specifying a web service as the datasource, but it
seems to work! I wouldn't recommend this if you have a really large data
structure, but might be useful if you don't have the resources (i.e. web
servers, developers, TIME) to create a web service out of some existing XML
structure.
Hope this helps!!
"brianpmccullough" wrote:
> Hello,
> Is it possible to pass an XML document as a string parameter to a Reporting
> Services 2005 report and use this report as a data source for the report?
> I know you can source from a web service, the problem is that the effort
> needed to convert what I have into a web service will be more than we would
> like to take on. Instead, I am trying to pass an XML structure as a string
> parameter to a report and then setting the Command Text like so:
> <Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
> TIA!!!
>|||Sorry...Correction...In step 7, do not specify a connection string...instead
leave it empty and instead this value should be used in the Query String!
"brianpmccullough" wrote:
> I figure out how to do what I am looking to do.
> 1. Create a new report and specify a sample XML file as your data source.
> This way you get all the DataSet <Field> elements you need automatically.
> 2. Create a new blank report (without the wizard using Add > New Item >
> Report).
> 3. Create a string report parameter to hold your XML data structure for the
> new report. I give it a default value of my sample XML used to create the
> report in step 1 for testing purposes.
> 4. Create a new DataSet (Data tab > <New DataSet...> from DataSet dropdown).
> 5. Name the DataSet (like "XmlSource").
> 6. Select XML from the Type dropdown.
> 7. Specify your connection string like so (use the appropriate parameter):
> ="<Query><XmlSource>" & Parameters!XmlSource.Value & "</XmlSource></Query>"
> 8. Go to your report you created in Step 1 in "Code View" and copy the
> <Fields> element from your <DataSet> element. Now paste this <Fields>
> element into the new report's <DataSet> element.
> 9. Using the fields, drag and drop to complete the layour of your report.
> This is sort of a hack to specifying a web service as the datasource, but it
> seems to work! I wouldn't recommend this if you have a really large data
> structure, but might be useful if you don't have the resources (i.e. web
> servers, developers, TIME) to create a web service out of some existing XML
> structure.
> Hope this helps!!
> "brianpmccullough" wrote:
> > Hello,
> >
> > Is it possible to pass an XML document as a string parameter to a Reporting
> > Services 2005 report and use this report as a data source for the report?
> >
> > I know you can source from a web service, the problem is that the effort
> > needed to convert what I have into a web service will be more than we would
> > like to take on. Instead, I am trying to pass an XML structure as a string
> > parameter to a report and then setting the Command Text like so:
> >
> > <Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>
> >
> > TIA!!!
> >

pass xml dataset to sql server

I'm not sure if this is the correct forum or not, if not can someone direct me to the correct one?

I have a dataset that is generate from C#, the data set is XML format (generated from .NET) and has the column names of the table and data that I need to insert into. Is it possible to pass the dataset as is and have a stored procedure parse out the data that I need?

example: the xml looks like this

<NewDataSet>

<Table>

<CustID>1</CustID>

<CustName>Larry</CustName>

</Table>

</NewDataSet>

Is possible to pass this dataset to SQL just like this and have a stored procedure parse it out?

Maybe something like this:

declare @.myXml xml
set @.myXml =
'<NewDataSet>
<Table>
<CustID>1</CustID>
<CustName>Larry</CustName>
</Table>
</NewDataSet>'

declare @.iDoc int

exec sp_xml_preparedocument @.iDoc output, @.myXml

select * from
openxml (@.iDoc, 'NewDataSet/Table', 1)
with ( CustID integer 'CustID/text()',
CustName varchar(10) 'CustName/text()'
)

/*
CustID CustName
-- -
1 Larry
*/

|||

Sure you can. There are new features in SQL Server 2005 regarding XML.

declare @.x xml

set @.x = N'

<NewDataSet>

<Table>

<CustID>1</CustID>

<CustName>Larry</CustName>

</Table>

</NewDataSet>

'

select

t.c.query('./CustID').value('.', 'int') as CustID,

t.c.query('./CustName').value('.', 'varchar(50)') as CustID

from

@.x.nodes('NewDataSet/Table') t(c)

go

AMB

Pass XML Data to use as Data Source?

Hello,

Is it possible to pass an XML document as a string parameter to a Reporting Services 2005 report and use this report as a data source for the report?

I know you can source from a web service, the problem is that the effort needed to convert what I have into a web service will be more than we would like to take on. Instead, I am trying to pass an XML structure as a string parameter to a report and then setting the Command Text like so:

<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>

TIA!!!

Hi Brian-

Yes, it is possible to do this. You will need to make the entire query string an expression, so that report processing will construct the query string first, then pass it to the data provider. For your example it would look like this:

="<Query><XmlData>" & Parameters!XmlData.Value & "</XmlData><ElementPath>MyRootElement</ElementPath></Query>"

Note that this will not execute in the designer data tab (It will show an error loading the query string). Ignore this error and manually add the data set values in the layout tab. This can be done by right clicking the your xml dataset in the DataSets pane and selecting add. The report will execute successfully through the preview pane and after publishing on the server.

Thanks, Jon

|||Why does this not run in the designer? Why do you have to publish it to get it to work?|||

It will run in the designer preview or a published report. However, the report designer query window/data tab does not have the ability to parse report parameters and will thus execute an invalid query.

-JonHP

Pass XML Data to use as Data Source?

Hello,

Is it possible to pass an XML document as a string parameter to a Reporting Services 2005 report and use this report as a data source for the report?

I know you can source from a web service, the problem is that the effort needed to convert what I have into a web service will be more than we would like to take on. Instead, I am trying to pass an XML structure as a string parameter to a report and then setting the Command Text like so:

<Query><XmlData>Parameters!XmlData.Value</XmlData><ElementPath>MyRootElement</ElementPath></Query>

TIA!!!

Hi Brian-

Yes, it is possible to do this. You will need to make the entire query string an expression, so that report processing will construct the query string first, then pass it to the data provider. For your example it would look like this:

="<Query><XmlData>" & Parameters!XmlData.Value & "</XmlData><ElementPath>MyRootElement</ElementPath></Query>"

Note that this will not execute in the designer data tab (It will show an error loading the query string). Ignore this error and manually add the data set values in the layout tab. This can be done by right clicking the your xml dataset in the DataSets pane and selecting add. The report will execute successfully through the preview pane and after publishing on the server.

Thanks, Jon

|||Why does this not run in the designer? Why do you have to publish it to get it to work?|||

It will run in the designer preview or a published report. However, the report designer query window/data tab does not have the ability to parse report parameters and will thus execute an invalid query.

-JonHP

Pass XML Data to a Stored Procedure

Does anyone know how to pass a XML File to a MS SQL 2005 Stored Procedure (INSERT/UPDATE), and how to create the stored procedure so it will accept the XML values using VB 2005.

Here is an example of the XML File.

Code:
<MYROOT>
<TableName>
<Field1>String</Field1>
<Field2>String</Field2>
<Field3>String</Field3>
</TableName>
</MYROOT>

Thank you.
bty The following link provided by Microsoft does not work in VB 2005, I have tried that

http://support.microsoft.com/default.aspx?scid=kb;en-us;555266

You can do this using XQuery to strip the values and pass them to the stored proc. You can view a good article on using XQuery here:

http://www.15seconds.com/issue/050803.htm

***

Download this free script (WSP Snapshot 1.0) to take snapshot sample of your web server(s) from anywhere/anytime using a browser. View the stats (cpu and disk stress, available memory, requests queued, request wait time and more) on a cellphone or PDA also. http://www.ifusionsoft.com

|||

Also check out Sushil's weblog on this ->

http://blogs.msdn.com/sushilc/archive/2004/08/03/207162.aspx

This explains the basics of sending XML to SQL 2005 as a parameter.

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
||||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.
Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
|
|||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>

Pass XML between SPROCs possible in SQL 2000? 2005?

A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hello Dave,

> Now I want to execute my stored procedure using the XML result of part
> A as the parameter for the sproc in part B. Is that possible? The
> only way I know how to do it right now is to return the result of A
> using ADO.NET, insert a root element (to make the XML valid), and then
> call the sproc created in part B with this xml as the parameter.
In 2005, you'd pass the XML as string to a SQLCLR stored proc. That stored
proc could parse the XML using a XmlReader, extact the command text and para
meter
values and have that proc execute other ones.
This is like the second time today that is idea has come up. Seems like it
time for some demo code... ;)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hello me, meet the real me,

> This is like the second time today that is idea has come up. Seems
> like it time for some demo code... ;)
Okay, here's the CLR-less version:
http://www.sqljunkies.com/WebLog/kt...er.aspx

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Pass text variable to stored proc

Hi,
I use a stored proc to load XML data into tables. The stored proc takes as
input the XML as a text parameter:
spLoadXML (@.XMLText text)
I have a table "tblXMLContent" with a column of text type containing the XML
text.
How do I pass the content of the column to the stored proc?
ThanksIt depends on how you are calling the stored procedure.
If you are calling it from the application (odbc,jdbc) then you should be
able to find the correlated datatype for a text column and pass it in.
If you are calling it from a stored procedure, you can not declare a
variable of type text and therefore I don't think you can unless the xml is
a parameter to that stored procedure as well. The only hack I have been able
to come up with is declare multiple varchar(8000) columns and parse the text
column 8000 bytes at a time into these variables
i.e.
Declare @.vc1 varchar(8000)
,@.vc2 varchar(8000)
Select @.vc1 = substring(text,1,8000)
,@.vc2 = substring(text,8001,8000)
from txtTable
Then I would use the exec statement that would call the proc
i.e.
exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
Hope this helps.
"fleo" <fleo@.discussions.microsoft.com> wrote in message
news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
> Hi,
> I use a stored proc to load XML data into tables. The stored proc takes
> as
> input the XML as a text parameter:
> spLoadXML (@.XMLText text)
> I have a table "tblXMLContent" with a column of text type containing the
> XML
> text.
> How do I pass the content of the column to the stored proc?
> Thanks|||Thanks JI
But OMG!!!! What a waste of time. I can't believe Microsoft haven't
thought of that. I mean they didn't test their OPENXML with normal XML files
'
Do you know if the process is easier in SQL Server 2005?
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>|||I use a DTS to get the text column value into a global variable and call the
sp.
Then I call the DTS from another sp.
"JI" wrote:

> It depends on how you are calling the stored procedure.
> If you are calling it from the application (odbc,jdbc) then you should be
> able to find the correlated datatype for a text column and pass it in.
> If you are calling it from a stored procedure, you can not declare a
> variable of type text and therefore I don't think you can unless the xml i
s
> a parameter to that stored procedure as well. The only hack I have been ab
le
> to come up with is declare multiple varchar(8000) columns and parse the te
xt
> column 8000 bytes at a time into these variables
> i.e.
> Declare @.vc1 varchar(8000)
> ,@.vc2 varchar(8000)
> Select @.vc1 = substring(text,1,8000)
> ,@.vc2 = substring(text,8001,8000)
> from txtTable
> Then I would use the exec statement that would call the proc
> i.e.
> exec ('up_procWithTextParam '+''''+ @.xml1+@.xml2+@.xml3+@.xml4+@.xml5+'''')
> Hope this helps.
> "fleo" <fleo@.discussions.microsoft.com> wrote in message
> news:2D87C93A-303F-4BFF-84B7-2BE6D00677A4@.microsoft.com...
>
>

pass several records at once for insert ?

If I was to pass several xml elements into a stored procedure (as an xml parameter), how could I then insert all the items into a table ?

Are you wanting to do something similar to what is done in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1501246&SiteID=1

Here is an example that uses NODES:

Code Snippet

declare @.myXml xml

set @.myXml = N'<marketData>
<date>19-Apr-2007 17:08:55</date>
<Rates>
<Rate code="USDCAD">
<values>
<value type="BID">1.1276</value>
<value type="ASK">1.1277</value>
<value type="MID">1.127649997783</value>
</values>
</Rate>
<Rate code="EURUSD">
<values>
<value type="BID">1.3607</value>
<value type="ASK">1.3608</value>
<value type="MID">1.36075</value>
</values>
</Rate>
</Rates>
</marketData>'

select r.value ('http://@.code', 'varchar(20)') as rateCode,
r.value ('./@.type', 'varchar(20)') as type,
r.value ('.', 'varchar(20)') as value
from @.myXml.nodes('/marketData/Rates/Rate/values/value') as x(r)

Pass record to user-defined function and create xml

Does anyone know if there is a way to pass a record from a select to a
user defined function in SQL Server 2000? The number and names of the
columns in the record will vary depending on the upload temp table
selected from... You should be able to see what I am trying to
accomplish bellow... If you have another idea, I'm open to
suggestions.
Example:
Declare @.CurrentFiscalYear smallint
Set @.CurrentFiscalYear = 2005
Create table ClaimEditLog(
TransactionId int,
XMLData varchar(7500),
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Create Table #tbClaimUploadData(
TransactionId int IDENTITY (1, 1) NOT NULL ,
FiscalYear smallint,
AmountTypeId int,
Amount money
)
Insert #tbClaimUploadData
Select 2005, 2, 556.98
Insert #tbClaimUploadData
Select 2006, 2, 56.90
Insert into ClaimEditLog
Select TransactionId,
dbo.UDF_ConvertRecordToXML(*),
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
>From #tbClaimUploadData
Where FiscalYear <> @.CurrentFiscalYear
-- UDF_ConvertRecordToXML - Convert record to XML
Select * from ClaimEditLog
--Output
2,
'<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalYea
r><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>',
'The Fiscal Year is incorrect.',
2006-05-31 06:41:32.527You could store the complete record and use a computed column to give a XML
representation of the output...
So your table definition would be this...
Create table ClaimEditLog(
TransactionId int,
FiscalYear smallint,
AmountTypeId int,
Amount money,
XMLData AS
'<XMLData><Record><TransactionId>'
+ CAST( TransactionId as varchar(20) )
+ '<FiscalYear>' + CAST( FiscalYear AS char(4) ) + '
etc...',
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Your insert would be...
insert claimeditlog (
TransactionId,
FiscalYear,
AmountTypeId,
Amount,
EditDesc,
LastUpdateId,
LastUpdate )
select TransactionId,
FiscalYear,
AmountTypeId,
Amount,
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
from #tbClaimUploadData
Select * from ClaimEditLog
And you'd get the XMLData output as text XML.
Make sense?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<porsch55@.yahoo.com> wrote in message
news:1149077706.631013.220560@.y43g2000cwc.googlegroups.com...
> Does anyone know if there is a way to pass a record from a select to a
> user defined function in SQL Server 2000? The number and names of the
> columns in the record will vary depending on the upload temp table
> selected from... You should be able to see what I am trying to
> accomplish bellow... If you have another idea, I'm open to
> suggestions.
> Example:
> Declare @.CurrentFiscalYear smallint
> Set @.CurrentFiscalYear = 2005
> Create table ClaimEditLog(
> TransactionId int,
> XMLData varchar(7500),
> EditDesc varchar(200),
> LastUpdateId varchar(20),
> LastUpdate datetime
> )
> Create Table #tbClaimUploadData(
> TransactionId int IDENTITY (1, 1) NOT NULL ,
> FiscalYear smallint,
> AmountTypeId int,
> Amount money
> )
> Insert #tbClaimUploadData
> Select 2005, 2, 556.98
> Insert #tbClaimUploadData
> Select 2006, 2, 56.90
> Insert into ClaimEditLog
> Select TransactionId,
> dbo.UDF_ConvertRecordToXML(*),
> 'The Fiscal Year is incorrect.',
> 'jporscha',
> GetDate()
> Where FiscalYear <> @.CurrentFiscalYear
> -- UDF_ConvertRecordToXML - Convert record to XML
> Select * from ClaimEditLog
> --Output
> 2,
> '<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalY
ear><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>'
,
> 'The Fiscal Year is incorrect.',
> 2006-05-31 06:41:32.527
>

pass list of id's in xml as i/p to stored proc

hii

I have a problem in when i pass xml as input to stored procedure. The problem i m facing is that :

Say i have a table which has id as primary key in it.Now in my sp i want to delete some rows from that table and in xml i am passin more than 1 id ( it can be four even).

But before deleteion i want to check that delete the with that ID iff the ID exists else raise error for the id w/c does not exist like 'This Id does not exist.'

what i want is to loop between the id's which are being passed as i/p to sp..

and delete the row if it exists elso diaplsy message this id does not exist..

somewhat like this

If Exists(Select ProfileName From Area52 Where Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) ))

Begin

Select * From Area52Docs

WHERE Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) )

DELETE FROM Area52Docs

WHERE Area52ID in

( Select T.Item.value( '@.Area52ID', 'uniqueidentifier')

FROM @.XMLString.nodes('Area52') AS T(Item) )

DELETE FROM Area52

FROM @.XMLString.nodes('Area52') AS T(Item)

where Area52id=T.Item.value( '@.Area52ID', 'uniqueidentifier')

select T.Item.value( '@.Area52ID', 'uniqueidentifier') FROM @.XMLString.nodes('Area52') AS T(Item)

Select 12

End

Else RaisError('This Record Does Not Exist',11,1)

the thing i want to do is as above..but its not workin..it works only for the first id int he list of id's

plzz do help...

Here it is,

Code Snippet

Declare @.Area52ID as XML

Set @.Area52ID = '<root>

<Area52 Area52ID="1"/>

<Area52 Area52ID="2"/>

<Area52 Area52ID="3"/>

</root>'

Declare @.Deletable Table

(

Area52ID int,

IsExists bit

);

Insert Into @.Deletable(Area52ID)

select

T.C.value('@.Area52ID','int') Area52ID

from

@.Area52ID.nodes('/root/Area52') as T(C)

Update @.Deletable

Set

IsExists = case when [Docs].Area52ID is null then 0 else 1 end

From

@.Deletable [Status]

Left Outer Join Area52Docs [Docs]

on [Status].Area52ID = [Docs].Area52ID;

Delete from Area52Docs

Where Exists (Select 1 From @.Deletable [Status]

Where [Status].Area52ID = [Area52Docs].Area52ID)

Select

Area52ID,

Case IsExists When 1 Then 'Area52ID =' + Cast(Area52ID as varchar) + ', Is Deleted Successfully'

When 0 Then 'Area52ID =' +Cast(Area52ID as varchar) + ', Is Not Exists' End

From

@.Deletable

|||

Hii

The approach to reach my problem is very correct as has been told by you.

But this is being done by first fetching the Id's in a table variable..that alternative i was also thinkin will work and its working..But what i was thinking if i could do this wtihout taking any intermediary table and direct from the xml...

Thanx

Supriya

pass large xmlstring to sql server 2000 stored procedure

Hi,
Can I use DBType.XML in .net 2.0 to pass xml string to a stored procedure in sql server 2000 ?
It's just that I know sql 2000 does not have xml type. If this is true then how is it possible to pass very large xml string to a stored procedure parameter? Remember that the xml strings may be more than 10000 characters.
At present, using .net 2.0, I am passing xml string using DbType.AnsiString with length of 50,000 and the stored procedure parameters that accepts the xmlstring is of type text.
I tried using DbType.XML in .net 2.0 but noticed that sql 2000 does not accept it.
Do you also know the max length of AnsiString?
Your thoughts please?

Thanks you

Hi,

Is there a specific reason why you need to pass the xml as one big string? i.e. Are you just writing the xml data to be stored as an xml document as a whole or can you break it up into "chunks"?

Breaking it up would require more time for the multiple calls to the database, but would solve the problem of length of string size as even 2005 has a max length.

Chris

|||Hi,
I do not wish to break the xml into smaller pieces.
Please note that my question is just to do with passing a large xmlstring.
If it is ok, please read my first post to see exactly what I am after.
Thanks for replying