September 17, 2015 at 5:48 pm
Hi everyone, I'd appreciate it if I could get some help with the query below as I'm struggling to get the correct result set. Thank you!!
-- SAMPLE XML
DECLARE @XML XML =
'
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
<ReportParameters>
<DataSources>
<DataSource Name="SRC_2015_09_17">
<DataSourceReference>/Data Sources/SRC_2015_09_17</DataSourceReference>
<rd:SecurityType>DataBase</rd:SecurityType>
<rd:DataSourceID>1234123412341234</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="dataset_0001">
<Query>
<DataSourceName>SRC_2015_09_17</DataSourceName>
<QueryParameters>
<QueryParameter Name="@FROM">
<Value>=Parameters!From.Value</Value>
</QueryParameter>
<QueryParameter Name="@TO">
<Value>=Parameters!To.Value</Value>
</QueryParameter>
<QueryParameter Name="@NAME">
<Value>=Parameters!Name.Value</Value>
</QueryParameter>
</QueryParameters>
<CommandType>StoredProcedure</CommandType>
<CommandText>SP_NAME</CommandText>
</Query>
</DataSet>
<DataSet Name="DATASET_2015_09_17">
<Query>
<DataSourceName>SRC_2015_09_17</DataSourceName>
<CommandText>SELECT 1</CommandText>
</Query>
<Fields>
<Field Name="Name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
<DataSet Name="DATASET_LIVE">
<Query>
<DataSourceName>SRC_2015_09_17</DataSourceName>
<CommandText>SELECT Id, Name FROM Customer</CommandText>
</Query>
<Fields>
<Field Name="Name">
<DataField>Name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Id">
<DataField>Id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportParameter Name="From">
<DataType>DateTime</DataType>
<Prompt>PROMPT_From</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
<ReportParameter Name="To">
<DataType>DateTime</DataType>
<Prompt>PROMPT_To</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
<ReportParameter Name="Name">
<DataType>String</DataType>
<Prompt>PROMPT_Name</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>DATASET_2015_09_17</DataSetName>
<ValueField>VAL_Name</ValueField>
<LabelField>LAB_Name</LabelField>
</DataSetReference>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
</Report>'
SELECT @XML
-- EXPECTED OUTPUT
SELECT 'From' AS Parameter
,'DateTime' AS ParameterDataType
,'PROMPT_From' AS ParameterPrompt
,'true' AS ParameterHidden
,NULL AS ParameterDatasetReference
,NULL AS ParameterDatasetReferenceTxt
UNION ALL
SELECT 'To' AS Parameter
,'DateTime' AS ParameterDataType
,'PROMPT_To' AS ParameterPrompt
,'true' AS ParameterHidden
,NULL AS ParameterDatasetReference
,NULL AS ParameterDatasetReferenceTxt
UNION ALL
SELECT 'Name' AS Parameter
,'String' AS ParameterDataType
,'PROMPT_Name' AS ParameterPrompt
,NULL AS ParameterHidden
,'DATASET_2015_09_17' AS ParameterDatasetReference
,'SELECT Id, Name FROM Customer' AS ParameterDatasetReferenceTxt
September 17, 2015 at 7:07 pm
Here's what you are looking for:
WITH
dataSets AS
(
SELECT
DatasetName = xxx.value('(@Name)[1]','varchar(100)'),
ParameterDatasetReferenceTxt = xxx.value('(.//*:CommandText/text())[1]','varchar(1000)')
FROM (VALUES(@XML)) T(X)
CROSS APPLY X.nodes('//*:DataSet') xxx(xxx)
),
params AS
(
SELECT
parameter = xx.value('(@Name)[1]','varchar(100)'),
parameterDataType = xx.value('(*:DataType/text())[1]','varchar(100)'),
ParameterPrompt = xx.value('(*:Prompt/text())[1]','varchar(100)'),
ParameterHidden = ISNULL(xx.value('(*:Hidden/text())[1]','varchar(100)'),'false'),
ParameterDatasetReference = xx.value('(.//*:DataSetName/text())[1]','varchar(100)')
FROM (VALUES(@XML)) T(X)
CROSS APPLY X.nodes('//*:ReportParameter') xx(xx)
)
SELECT
parameter,
parameterDataType,
ParameterPrompt,
ParameterHidden,
ParameterDatasetReference,
ParameterDatasetReferenceTxt
FROM params p
LEFT JOIN dataSets d ON p.ParameterDatasetReference = d.DatasetName;
You query in the expected output is wrong based on the XML you posted BTW. The query associated with the dataset named "DATASET_2015_09_17" is: SELECT 1. The query "SELECT Id, Name FROM Customer" is associated with the Dataset named DATASET_LIVE.
-- Itzik Ben-Gan 2001
September 18, 2015 at 5:46 pm
Thank you very much Alan.
September 18, 2015 at 11:10 pm
kiril.lazarov.77 (9/18/2015)
Thank you very much Alan.
No problem.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply