How to get at value?
-
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)
; -
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).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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
-
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" >
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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.
-
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.
-
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?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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
-
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...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
yes, I changed x.value to d.value; I'll just have to live with it. thank you for your help Brian.
-
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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
-
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;
Have a good weekend Brian, and thank you for your help. regards, beth
-
My output looks different than yours for sure:
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
@@servernameAS [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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply