lklein (5/8/2008)
ok - adding the code.Return... enable it to run - however with no results.Is this right for in my sp
C=my parameter name
Customer=label
Customer_Number=int value
INNER JOIN
@C.nodes('/C/Customer') as m(item) on (CS.CUSTOMER_NUMBER) = m.item.value('Customer_Number[1]','integer')
please... anyone... thank you!
As you wrote the query, you should have a parameter in your stored procedure defined as @C XML.
I don't see anything wrong with your query. If it still doesn't return anything, try adding this to your procedure. When you run the report, you'll see what you're sending it.
[Code]
declare @c1 varchar(max)
set @c1 = convert(varchar(max), @C)
RaisError (@c1, 16, 1)
[/Code]
At this point, you can copy the XML string to SSMS and debug your proc from there.
Also, you can run this to see if the XML string is processing correctly:
[Code]
select m.item.value('Customer_Number[1]', 'integer') CustomerNumber
from @C.nodes('/C/Customer') AS m(item)
[/Code]
Just to clarify:
C=Root
Customer=Node
Customer_Number=Element
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes