Does SSRS 2008 support the XML Datatype in SQL Queries?

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