Xpath SQL query help please

  • 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

  • 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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thank you very much Alan.

  • kiril.lazarov.77 (9/18/2015)


    Thank you very much Alan.

    No problem.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- 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