I have an query that uses the XML
data type. (You can see the query here.)
Just to be clear that means that my query has something like this in it:
declare @xmlDoc XML
When I try to paste my query in as a Dataset for a SQL Server Reporting Services Report in BIDS (Visual Studio 2008) a dialog pops asking me to define my parameters:
The problem is that I don't have any Parameters! I define and use @xmldoc in the query (it runs with no issues in SSMS).
It does not really seem to matter what I enter here. This is always the next dialog box:
"OK" closes the Dataset properties and I get no fields setup for me. "Cancel" gets me back to the properties to try again. If I put in a query without the XML
data type then it works fine.
I am stumped... I can only conclud开发者_如何学Pythone that SSRS does not support the XML data type.
Is that true? Is there a work around?
You should remove variable @xmldoc and use
CAST(@DocParam as XML)
in INSERT query like this:
; With c as(SELECT CAST(@DocParam as XML) DocParam)
SELECT TBL.SParam.value('local-name(.)[1]', 'varchar(50)') as Name,
TBL.SParam.value('(.)[1]', 'varchar(50)') as Value1,
TBL2.SParam2.value('(.)[1]', 'varchar(50)') as Value2,
TBL3.SParam3.value('(.)[1]', 'varchar(50)') as Value3,
TBL4.SParam4.value('(.)[1]', 'varchar(50)') as Value4,
TBL5.SParam5.value('(.)[1]', 'varchar(50)') as Value5
FROM C
CROSS APPLY
DocParam.nodes('/NewDataSet/Table1[1]/*') AS TBL(SParam)
left JOIN DocParam.nodes('/NewDataSet/Table1[2]/*') AS TBL2(SParam2)
ON TBL.SParam.value('local-name(.)[1]', 'varchar(50)')
= TBL2.SParam2.value('local-name(.)[1]', 'varchar(50)')
left JOIN DocParam.nodes('/NewDataSet/Table1[3]/*') AS TBL3(SParam3)
ON TBL.SParam.value('local-name(.)[1]', 'varchar(50)')
= TBL2.SParam2.value('local-name(.)[1]', 'varchar(50)')
left JOIN DocParam.nodes('/NewDataSet/Table1[4]/*') AS TBL4(SParam4)
ON TBL.SParam.value('local-name(.)[1]', 'varchar(50)')
= TBL2.SParam2.value('local-name(.)[1]', 'varchar(50)')
left JOIN DocParam.nodes('/NewDataSet/Table1[5]/*') AS TBL5(SParam5)
ON TBL.SParam.value('local-name(.)[1]', 'varchar(50)')
= TBL2.SParam2.value('local-name(.)[1]', 'varchar(50)')
Second option is to have 2 DataSets, first sets @xmldoc parameter value and second use parameter in query
An third option is to use stored procedure with all that logic inside
精彩评论