Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using XML to pass Multi-Select parameters from SSRS to SQL Server


Using XML to pass Multi-Select parameters from SSRS to SQL Server

Author
Message
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
The following code (based off of your query) works for me. Note that I build the XML string by hand, but your XML string should look similiar.


declare @C XML
set @C = ' '

select m.item.value('CUSTOMER_NUMBER[1]','integer') [Customer_Number]
from @C.nodes('/C/CUSTOMER') as m(item)



The result set I get back is:
Customer_Number
--------------
1
2

There must be a problem with the XML string that you are passing to the @C parameter.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
lklein (5/8/2008)
This is so frustrating... yikes - I get an error running that just in design view right?

Yes, the RaisError raises an error. In Preview mode, you'll see the xml line that you're passing to the procedure.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
This is the function in the code section - which is different from the beginning article.

Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As String
'**************************************************************************
' Returns an XML string by using the specified values.
' Parameters:
' MultiValueList - a multi value list from SSRS
' Root, Node, Element - String to use in building the XML string
'**************************************************************************
Dim ReturnString = ""
Dim sParamItem As Object
ReturnString = " "
For Each sParamItem In MultiValueList
ReturnString &= " "
Next
ReturnString &= " "
Return (ReturnString)
End Function
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
lklein (5/8/2008)
This is the function in the code section - which is different from the beginning article.


If you're not using the function in the beginning article, I don't know what you're doing. And the post you sent is removing all of the xml tags, so all I see is a string of spaces.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Adriaan Davel
Adriaan Davel
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 13
Hi Wayne,

Nice one, have you looked at the performance of using the XML joins in your stored procedure?

I did quite a bit of performance testing with the "other" methods [url=http://www.sqlservercentral.com/articles/Development/3138/][/url] and the results were quite interesting, would love to see how XML performs with this.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
Adriaan Davel (5/9/2008)

Hi Wayne,

Nice one, have you looked at the performance of using the XML joins in your stored procedure?



In my "casual" testing, XML datasets up to about 1000 "records" performed pretty fast. Beyond 1000 it started slowing down; when it got to 5000 it was crawling, and I stopped one query with > 20,000 elements after about an hour. Most of the XML datasets I use are in dealing with SSRS, where the users want to be able to select > 1 option. Most of these selections are kept down to 15000. but, they've been told...

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
Wayne,
I've put the original code back in the code property and have the same results - nothing returns. However, now that it's back to your code - is there anything on the sql server that needs to be set to allow XML?

Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String, ByVal Element As String) As String
Dim ReturnString = ""
Dim sParamItem As Object
ReturnString = " "
For Each sParamItem In MultiValueList
ReturnString &= " "
Next
ReturnString &= " "
Return (ReturnString)
End Function
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
Hello again...

As you can see, posting XML strings is stripping everything out.
I've recoded this to build the string in a more complex method. Hopefully, it will post okay.

This code, when I run it, returns 2 records. What do you get?


declare @start char(1), @end char(1), @Root char(1), @Node char(8), @Element char(15)
select @Start = char(60), @end = char(62), @Root = 'C', @Node = 'CUSTOMER', @Element = 'CUSTOMER_NUMBER'

declare @C XML
set @C = @Start + @Root + @End +
@Start + @Node + @End + @Start + @Element + @End + '1' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End +
@Start + @Node + @End + @Start + @Element + @End + '2' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End +
@Start + '/' + @Root + @End

select m.item.value('CUSTOMER_NUMBER[1]','integer') [Customer_Number]
from @C.nodes('/C/CUSTOMER') as m(item)



Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

lklein
lklein
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 286
Yes - I get 1,2
nzarrad
nzarrad
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 63
WayneS (5/12/2008)
Hello again...

As you can see, posting XML strings is stripping everything out.
I've recoded this to build the string in a more complex method. Hopefully, it will post okay.

This code, when I run it, returns 2 records. What do you get?


declare @start char(1), @end char(1), @Root char(1), @Node char(8), @Element char(15)
select @Start = char(60), @end = char(62), @Root = 'C', @Node = 'CUSTOMER', @Element = 'CUSTOMER_NUMBER'

declare @C XML
set @C = @Start + @Root + @End +
@Start + @Node + @End + @Start + @Element + @End + '1' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End +
@Start + @Node + @End + @Start + @Element + @End + '2' + @Start + '/' + @Element + @End + @Start + '/' + @Node + @End +
@Start + '/' + @Root + @End

select m.item.value('CUSTOMER_NUMBER[1]','integer') [Customer_Number]
from @C.nodes('/C/CUSTOMER') as m(item)




I get 1 & 2 too.
But I haven't managed to pass the multi value paramater as an XML to my sproc.
Did anyone got it to work?
This is my thread:
http://www.sqlservercentral.com/Forums/Topic1115348-150-1.aspx#bm1115415
Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search