Xpath SQL query help please

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  September 17, 2015 at 5:48 pm

  #303187

  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

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 17, 2015 at 7:07 pm

  #1827605

  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

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  September 18, 2015 at 5:46 pm

  #1827866

  Thank you very much Alan.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  September 18, 2015 at 11:10 pm

  #1827873

  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