How to get at value?

  • beth-447786

    SSC-Addicted

    Points: 458

    Hello,

    I want to pluck out the values for "<rd:DbType>" and "<rd:IsMultiValued>" but could not figure out the right syntax (don't know how to reference "<rd:").

    Below is the XML code fragment, followed by the select statement.

    Any help is appreciated.

    thank you, beth

    - <SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
    <Description />
    - <DataSet Name="dsCompanies">
    - <Query>
    <DataSourceReference>[datasource]</DataSourceReference>
    - <DataSetParameters>
    - <DataSetParameter Name="@Year">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    </DataSetParameter>
    - <DataSetParameter Name="@Months">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    </DataSetParameters>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>[stored procedure]</CommandText>
    </Query>
    ;with
    xmlnamespaces (
    default 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition',
    'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner' as rd
    )
    select
    'SharedDataSet' as ' ',
    a.Server, a.[Report],
    DatasetName = x.value('@Name', 'varchar(250)'),
    DataSourceName = x.value('(Query/DataSourceReference)[1]','varchar(250)'),
    DataSetParameterName = d.value('@Name', 'varchar(250)'),
    ReadOnly = x.value('(Query/DataSetParameters/DataSetParameter/ReadOnly)[1]','varchar(max)'),
    Nullable = x.value('(Query/DataSetParameters/DataSetParameter/Nullable)[1]','varchar(max)'),
    OmitFromQuery = x.value('(Query/DataSetParameters/DataSetParameter/OmitFromQuery)[1]','varchar(max)'),
    CommandType = x.value('(Query/CommandType)[1]','varchar(250)'),
    CommandText = x.value('(Query/CommandText)[1]','varchar(max)')
    from (
    select
    @@servername as 'Server', [Path] as 'Report',
    cast(cast(c.Content as varbinary(max)) as xml) as 'ContentXML'
    from
    dbo.Catalog c with (nolock)
    where
    c.ItemID = '[ItemID]'
    ) a
    cross apply ContentXML.nodes(
    '/SharedDataSet/DataSet'
    ) r(x)
    outer apply x.nodes(
    'Query/DataSetParameters/DataSetParameter'
    ) param(d)
    ;
  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    for those who want to run this without having to create a table and put the XML into it:

    DECLARE @xml XML = '<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
    <Description />
    <DataSet Name="dsCompanies">
    <Query>
    <DataSourceReference>[datasource]</DataSourceReference>
    <DataSetParameters>
    <DataSetParameter Name="@Year">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    </DataSetParameter>
    <DataSetParameter Name="@Months">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    </DataSetParameters>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>[stored procedure]</CommandText>
    </Query>
    </DataSet>
    </SharedDataSet>'


    ;with
    xmlnamespaces (
    default 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition',
    'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner' as rd
    )
    select
    'SharedDataSet' as ' ',
    a.Server, --a.[Report],
    DatasetName = x.value('@Name', 'varchar(250)'),
    DataSourceName = x.value('(Query/DataSourceReference)[1]','varchar(250)'),
    DataSetParameterName = d.value('@Name', 'varchar(250)'),
    ReadOnly = x.value('(Query/DataSetParameters/DataSetParameter/ReadOnly)[1]','varchar(max)'),
    Nullable = x.value('(Query/DataSetParameters/DataSetParameter/Nullable)[1]','varchar(max)'),
    OmitFromQuery = x.value('(Query/DataSetParameters/DataSetParameter/OmitFromQuery)[1]','varchar(max)'),
    CommandType = x.value('(Query/CommandType)[1]','varchar(250)'),
    CommandText = x.value('(Query/CommandText)[1]','varchar(max)')
    from (
    select
    @@servername as 'Server',-- [Path] as 'Report',
    cast(cast(@xml as varbinary(max)) as xml) as 'ContentXML'
    --from
    -- dbo.Catalog c with (nolock)
    --where
    -- c.ItemID = '[ItemID]'
    ) a
    cross apply ContentXML.nodes(
    '/SharedDataSet/DataSet'
    ) r(x)
    outer apply x.nodes(
    'Query/DataSetParameters/DataSetParameter'
    ) param(d)
    ;

    XML slightly modified to be properly formed (missing 2 closing tags).

  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    This is what you need to add:

      DBType = x.value('(Query/DataSetParameters/DataSetParameter/rd:DbType)[1]','VARCHAR(MAX)'),
    IsMultiValued = x.value('(Query/DataSetParameters/DataSetParameter/rd:IsMultiValued)[1]','VARCHAR(MAX)')

    Full query:

    DECLARE @xml XML = '<SharedDataSet xmlns:rd="http://schemas.microsoft.com/sqlserver/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition" >
    <Description />
    <DataSet Name="dsCompanies">
    <Query>
    <DataSourceReference>[datasource]</DataSourceReference>
    <DataSetParameters >
    <DataSetParameter Name="@Year" >
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    </DataSetParameter>
    <DataSetParameter Name="@Months">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    </DataSetParameters>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>[stored procedure]</CommandText>
    </Query>
    </DataSet>
    </SharedDataSet>'


    ;with
    xmlnamespaces (
    default 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition',
    'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner' as rd
    )
    select
    'SharedDataSet' as ' ',
    a.Server, --a.[Report],
    DatasetName = x.value('@Name', 'varchar(250)'),
    DataSourceName = x.value('(Query/DataSourceReference)[1]','varchar(250)'),
    DataSetParameterName = d.value('@Name', 'varchar(250)'),
    ReadOnly = x.value('(Query/DataSetParameters/DataSetParameter/ReadOnly)[1]','varchar(max)'),
    Nullable = x.value('(Query/DataSetParameters/DataSetParameter/Nullable)[1]','varchar(max)'),
    OmitFromQuery = x.value('(Query/DataSetParameters/DataSetParameter/OmitFromQuery)[1]','varchar(max)'),
    CommandType = x.value('(Query/CommandType)[1]','varchar(250)'),
    CommandText = x.value('(Query/CommandText)[1]','varchar(max)'),
    DBType = x.value('(Query/DataSetParameters/DataSetParameter/rd:DbType)[1]','VARCHAR(MAX)'),
    IsMultiValued = x.value('(Query/DataSetParameters/DataSetParameter/rd:IsMultiValued)[1]','VARCHAR(MAX)')
    FROM (
    select
    @@servername as 'Server',-- [Path] as 'Report',
    cast(cast(@xml as varbinary(max)) as xml) as 'ContentXML'
    --from
    -- dbo.Catalog c with (nolock)
    --where
    -- c.ItemID = '[ItemID]'
    ) a
    cross apply ContentXML.nodes(
    '/SharedDataSet/DataSet'
    ) r(x)
    outer apply x.nodes(
    'Query/DataSetParameters/DataSetParameter'
    ) param(d)
    ;

     

    something to note - I updated your XML slightly.  The URL's are case sensitive.

  • beth-447786

    SSC-Addicted

    Points: 458

    Brian,

    I should have said that I already tried that, and I get NULL for DBType and IsMultiValued;

    DataSetParameterName ReadOnly Nullable OmitFromQuery DBType IsMultiValued

    @Year false false false NULL NULL

    @Months false false false NULL NULL

    beth

  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    even with the corrected XML that I put there?

    If you run the query I provided in full, it runs and gives you the results you want.

    I ran this on SQL Server 2016 and I got results I expect... mostly.  A few little issues with the query though, so this is the new query:

    select
    'SharedDataSet' as ' ',
    a.Server, --a.[Report],
    DatasetName = x.value('@Name', 'varchar(250)'),
    DataSourceName = x.value('(Query/DataSourceReference)[1]','varchar(250)'),
    DataSetParameterName = d.value('@Name', 'varchar(250)'),
    ReadOnly = d.value('(ReadOnly)[1]','varchar(max)'),
    Nullable = d.value('(Nullable)[1]','varchar(max)'),
    OmitFromQuery = d.value('(OmitFromQuery)[1]','varchar(max)'),
    CommandType = x.value('(Query/CommandType)[1]','varchar(250)'),
    CommandText = x.value('(Query/CommandText)[1]','varchar(max)'),
    DBType = d.value('(rd:DbType)[1]','VARCHAR(MAX)'),
    IsMultiValued = d.value('(rd:IsMultiValued)[1]','VARCHAR(MAX)')
    FROM (
    select
    @@servername as 'Server',-- [Path] as 'Report',
    cast(cast(@xml as varbinary(max)) as xml) as 'ContentXML'
    --from
    -- dbo.Catalog c with (nolock)
    --where
    -- c.ItemID = '[ItemID]'
    ) a
    cross apply ContentXML.nodes(
    '/SharedDataSet/DataSet'
    ) r(x)
    outer apply x.nodes(
    'Query/DataSetParameters/DataSetParameter'
    ) param(d)
    ;

    It may need a few little tweaks with your final data depending on IF the x.value's that remain are giving you the expected result or not.

    NOTE - it uses d.value for most things rather than x.value because the way it is written, x.value will return the FIRST value encountered rather than the one that applies to the row you selected.

    And you NEED to update your XML as it has a typo.  Your XML first line:

    <SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition" >

    Corrected XML (NOTE - sqlserver is lowercase)

    <SharedDataSet xmlns:rd="http://schemas.microsoft.com/sqlserver/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition" >

     

  • beth-447786

    SSC-Addicted

    Points: 458

    Brian,

    I am running on an earlier server version;

    Microsoft SQL Server 2008 R2 (SP3-GDR) (KB4057113) - 10.50.6560.0 (X64) Dec 28 2017 15:03:48 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)

    Don't know if this explains why our results are different.

    I don't understand your point about uppercase/lowercase. Attached is the full XML - I only showed a fragment in my original post because I thought that was enough for someone to figure out how to get the information I couldn't get.

    I switched to d.value as you suggested; I get the same results (including NULL for the two rd: values). But, it's hard to know if x.value was producing the wrong results given that ReadOnly, Nullable, and OmitFromQuery are all "false" for both parameters - I'll need to find a shared dataset that has a mixture.

  • beth-447786

    SSC-Addicted

    Points: 458

    I guess you can't attach .xml files.  Trying .txt (same content). If this works, you can save it with a .xml extension, then open it with a browser to see it formatted so it's readable.

    Attachments:
    You must be logged in to view attached files.
  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    What I meant about capital letters is exactly that. Capital letters in the URL.  SQLServer is not the same thing as sqlserver. Notice the capital letters?

     

    Also, did you try running the exact query I posted including the variable @xml?

  • beth-447786

    SSC-Addicted

    Points: 458

    Brian,

    Just tried your @xml variable/cte, and yes, it works, but ...

    I have reworked my script, which I use to print out information about many different types of items in ReportServer.dbo.Catalog, not just shared data sets, to dynamically set variable @reportdesigner varchar(max). I had been hard-wiring this variable to;

    http://schemas.microsoft.com/sqlserver/reporting/reportdesigner

    but mindful of what you said about case, now I am setting the variable value dynamically from the ReportServer.dbo.Catalog.Content column, which BTW, is datatype image, not xml. The dynamically set @reportdesigner value for the dsCompanies item is actually;

    http://schemas.microsoft.com/SQLServer/reporting/reportdesigner

    Now that I've done that, I am getting the correct value for IsMultiValued, but DBType is still NULL. Something else is wrong.

    thank you

  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    Are you using X.VALUE or D.VALUE?  your X.VALUE ones will give you the first result found, the D.VALUE ones will give you one result per row.

    Where I work, we do not use shared data sets in our reports, so if I try running your query on the report server, I get no results.  So reproducing your problem without actual data is a bit tricky...

  • beth-447786

    SSC-Addicted

    Points: 458

    yes, I changed x.value to d.value; I'll just have to live with it. thank you for your help Brian.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    We should be able to get that value... I don't get it... I changed the XML to be this:

    DECLARE @xml XML = '<SharedDataSet xmlns:rd="http://schemas.microsoft.com/sqlserver/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition" >
    <Description />
    <DataSet Name="dsCompanies">
    <Query>
    <DataSourceReference>[datasource]</DataSourceReference>
    <DataSetParameters >
    <DataSetParameter Name="@Year" >
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    </DataSetParameter>
    <DataSetParameter Name="@Months">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    </DataSetParameters>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>[stored procedure]</CommandText>
    </Query>
    </DataSet>
    </SharedDataSet>'

    NOTE - the change is I removed the rd:DbType from the first row.

    and ran what I had (the one with the d.value's) and it brings back NULL for for 1 and a value for row 2; both of which are the expected values.

    As a random thought - could it be that the value is NULL in most cases and you just don't see the value while scrolling through a large data set?

    Failing that, could you post a bit more sample data and I can try to help some more if you like.

  • beth-447786

    SSC-Addicted

    Points: 458

    See attachments. A different Shared Data Set with more parameters; I made sure to set some of the values differently so some are true, some are false. Added a default value to the @Year parameter, and selected (unspecified) for the Data Type on @Companies. For me, DBType continues to be NULL for all parameters, with the only legit null is for @Companies.

    beth

    Attachments:
    You must be logged in to view attached files.
  • beth-447786

    SSC-Addicted

    Points: 458

    Come on, a .txt file isn't allowed? sigh ...

    Here is the shared data set;

    <?xml version="1.0" encoding="utf-8"?>
    <SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
    <Description />
    <DataSet Name="dsNaturalAccounts">
    <Query>
    <DataSourceReference>WarehouseServer</DataSourceReference>
    <DataSetParameters>
    <DataSetParameter Name="@Year">
    <DefaultValue>2020</DefaultValue>
    <ReadOnly>true</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    </DataSetParameter>
    <DataSetParameter Name="@Months">
    <ReadOnly>false</ReadOnly>
    <Nullable>true</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@Companies">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@Locations">
    <ReadOnly>false</ReadOnly>
    <Nullable>true</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@Departments">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>true</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@PracticeGroups">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    </DataSetParameters>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>DataWarehouse.dbo.NaturalAccounts_DDL</CommandText>
    </Query>
    <Fields>
    <Field Name="GLNaturalAccount">
    <DataField>GLNaturalAccount</DataField>
    <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="GLNaturalAccountName">
    <DataField>GLNaturalAccountName</DataField>
    <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="GLNaturalAccountCdName">
    <DataField>GLNaturalAccountCdName</DataField>
    <rd:TypeName>System.String</rd:TypeName>
    </Field>
    </Fields>
    </DataSet>
    </SharedDataSet>

    and here is the output I get;

    dsNaturalAccounts_output

    Have a good weekend Brian, and thank you for your help. regards, beth

  • Mr. Brian Gale

    SSC-Insane

    Points: 22926

    My output looks different than yours for sure:

    Untitled

    I ran the following:

    DECLARE @xml XML = '<?xml version="1.0" encoding="utf-8"?>
    <SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
    <Description />
    <DataSet Name="dsNaturalAccounts">
    <Query>
    <DataSourceReference>WarehouseServer</DataSourceReference>
    <DataSetParameters>
    <DataSetParameter Name="@Year">
    <DefaultValue>2020</DefaultValue>
    <ReadOnly>true</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    </DataSetParameter>
    <DataSetParameter Name="@Months">
    <ReadOnly>false</ReadOnly>
    <Nullable>true</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@Companies">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@Locations">
    <ReadOnly>false</ReadOnly>
    <Nullable>true</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@Departments">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>true</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    <DataSetParameter Name="@PracticeGroups">
    <ReadOnly>false</ReadOnly>
    <Nullable>false</Nullable>
    <OmitFromQuery>false</OmitFromQuery>
    <rd:DbType>String</rd:DbType>
    <rd:IsMultiValued>true</rd:IsMultiValued>
    </DataSetParameter>
    </DataSetParameters>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>DataWarehouse.dbo.NaturalAccounts_DDL</CommandText>
    </Query>
    <Fields>
    <Field Name="GLNaturalAccount">
    <DataField>GLNaturalAccount</DataField>
    <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="GLNaturalAccountName">
    <DataField>GLNaturalAccountName</DataField>
    <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="GLNaturalAccountCdName">
    <DataField>GLNaturalAccountCdName</DataField>
    <rd:TypeName>System.String</rd:TypeName>
    </Field>
    </Fields>
    </DataSet>
    </SharedDataSet>';
    WITH XMLNAMESPACES
    (
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition'
    , 'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner' AS [rd]
    )
    SELECT
    'SharedDataSet' AS [ ]
    , [a].[Server]
    --,a.[Report]
    , [x].[value]( '@Name'
    , 'varchar(250)'
    ) AS [DatasetName]
    , [x].[value]( '(Query/DataSourceReference)[1]'
    , 'varchar(250)'
    ) AS [DataSourceName]
    , [d].[value]( '@Name'
    , 'varchar(250)'
    ) AS [DataSetParameterName]
    , [d].[value]( '(ReadOnly)[1]'
    , 'varchar(max)'
    ) AS [ReadOnly]
    , [d].[value]( '(Nullable)[1]'
    , 'varchar(max)'
    ) AS [Nullable]
    , [d].[value]( '(OmitFromQuery)[1]'
    , 'varchar(max)'
    ) AS [OmitFromQuery]
    , [x].[value]( '(Query/CommandType)[1]'
    , 'varchar(250)'
    ) AS [CommandType]
    , [x].[value]( '(Query/CommandText)[1]'
    , 'varchar(max)'
    ) AS [CommandText]
    , [d].[value]( '(rd:DbType)[1]'
    , 'VARCHAR(MAX)'
    ) AS [DBType]
    , [d].[value]( '(rd:IsMultiValued)[1]'
    , 'VARCHAR(MAX)'
    ) AS [IsMultiValued]
    FROM
    (
    SELECT
    @@servername AS [Server] -- [Path] as 'Report',
    , CAST(REPLACE( CAST(CAST(CAST(@xml AS VARBINARY(MAX)) AS XML) AS VARCHAR(MAX))
    , 'SQLS'
    , 'sqls'
    ) AS XML) AS [ContentXML]
    --from
    -- dbo.Catalog c with (nolock)
    --where
    -- c.ItemID = '[ItemID]'
    ) AS [a]
    CROSS APPLY [ContentXML].[nodes]('/SharedDataSet/DataSet') AS [r]([x])
    OUTER APPLY [x].[nodes]('Query/DataSetParameters/DataSetParameter') AS [param]([d]);

    Things that are different from the one I posted originally are:

    1 - I am adding an additional 2 CASTS and a REPLACE to the original where we are getting the IMAGE, cast it to VARBINARY, then to XML, then to VARCHAR(MAX), then do a REPLACE to change all SQLS to sqls (ie uppercase to lowercase) then finally CAST it back to XML so we can parse it.

    2 - I ran SQL Prompt SQL Format on it so the code is a bit easier to read and follows my normal coding conventions.

    But otherwise I didn't change anything and I get "good" results.  The columns with NULL, that is expected as no value exists for that property for that row.

    The only thing I can think of is MAYBE it is a SQL versioning issue?  I ran the above code on SQL Server 2008 R2 and SQL Server 2016 and got the same results on both.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply