XQuery union
-
I have 2 types of reports in the report server, one built with Report Builder 3.0 and the other with Visual Studio. The 2 namespaces are different so I can run the following statements indvdually
use ReportServer
WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
--Get Variables
SELECT
[Name] as ReportName,
[Path] as ReportPath,
'RB' as ReportDT,
substring(x.value('@Name[1]', 'VARCHAR(50)'),4,255) AS VariableName,
x.value('Value[1]','VARCHAR(1000)') AS VariableValue
FROM (
select [Name], [Path],'RB' as ReportDT,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog WHERE [Type]=2
) a
CROSS APPLY reportXML.nodes('/Report/Variables/Variable') r(x)
where x.value('@Name[1]', 'VARCHAR(50)') like 'var%'
go
WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
--Get Variables
SELECT
[Name] as ReportName,
[Path] as ReportPath,
'VS' as ReportDT,
substring(x.value('@Name[1]', 'VARCHAR(50)'),4,255) AS VariableName,
x.value('Value[1]','VARCHAR(1000)') AS VariableValue
FROM (
select [ItemID], [Name], [Path],'RB' as ReportDT,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog WHERE [Type]=2
) a
CROSS APPLY reportXML.nodes('/Report/Variables/Variable') r(x)
where x.value('@Name[1]', 'VARCHAR(50)') like 'var%'
go
What I need to do is union the 2 xqueries together, the question is how?
I have tried a simple union like this:
use ReportServer
WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
[Name] as ReportName,
[Path] as ReportPath,
'RB' as ReportDT,
substring(x.value('@Name[1]', 'VARCHAR(50)'),4,255) AS VariableName,
x.value('Value[1]','VARCHAR(1000)') AS VariableValue
FROM (
select [Name], [Path],'RB' as ReportDT,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog WHERE [Type]=2
) a
CROSS APPLY reportXML.nodes('/Report/Variables/Variable') r(x)
where x.value('@Name[1]', 'VARCHAR(50)') like 'var%'
UNION
WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
[Name] as ReportName,
[Path] as ReportPath,
'VS' as ReportDT,
substring(x.value('@Name[1]', 'VARCHAR(50)'),4,255) AS VariableName,
x.value('Value[1]','VARCHAR(1000)') AS VariableValue
FROM (
select [ItemID], [Name], [Path],'RB' as ReportDT,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog WHERE [Type]=2
) a
CROSS APPLY reportXML.nodes('/Report/Variables/Variable') r(x)
where x.value('@Name[1]', 'VARCHAR(50)') like 'var%'
But it barks at the UNION keyword:
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'UNION'.
Msg 319, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I also tried to encapsulate each xquery with a select * from() but that failed too.
-
It's not so much the UNION that's the problem as the second WITH. I don't think you need to specify the namespaces for each query, so just omit that for the second query, like so:
WITH XMLNAMESPACES (DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
[Name] as ReportName,
[Path] as ReportPath,
'RB' as ReportDT,
substring(x.value('@Name[1]', 'VARCHAR(50)'),4,255) AS VariableName,
x.value('Value[1]','VARCHAR(1000)') AS VariableValue
FROM (
select [Name], [Path],'RB' as ReportDT,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog WHERE [Type]=2
) a
CROSS APPLY reportXML.nodes('/Report/Variables/Variable') r(x)
where x.value('@Name[1]', 'VARCHAR(50)') like 'var%'
UNION
SELECT
[Name] as ReportName,
[Path] as ReportPath,
'VS' as ReportDT,
substring(x.value('@Name[1]', 'VARCHAR(50)'),4,255) AS VariableName,
x.value('Value[1]','VARCHAR(1000)') AS VariableValue
FROM (
select [ItemID], [Name], [Path],'RB' as ReportDT,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog WHERE [Type]=2
) a
CROSS APPLY reportXML.nodes('/Report/Variables/Variable') r(x)
where x.value('@Name[1]', 'VARCHAR(50)') like 'var%'
Cheers!
-
Problem is that the namespaces are different so I need to declare them as the XML is different between Report Builder 3.0 and Visual Studio BIDS 2008 r2
WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
If I query a report developed in Report Builder 3.0 I have to use 2010/01 namespace or I get no rows and the same for VS, I have to use 2008/01/
-
Ah, indeed. I told myself to double-check to make sure both were the same, and apparently failed. I picked the wrong day to stop drinking coffee :crazy:
Have you tried declaring the namespace within the XQuery instead of using WITH XMLNAMESPACES (as shown at https://msdn.microsoft.com/en-us/library/ms187013.aspx)? That should allow you to just UNION the results.
You can also probably do it using the one initial WITH XMLNAMESPACES clause if you list all of the namespaces, specify prefixes for each of them, and then use the prefixes in the XQuery, as outlined at https://msdn.microsoft.com/en-us/library/ms177400.aspx.
I don't have access to an instance to test specific code, but either of those approaches should make the UNION workable.
I hope this helps.
Cheers!
-
I think I am close but not close enough 🙁
Instead of 3 rows I am expecting I get 6 with some null values as DataSourceReference for 3 rows. I think it has something to do with q.value('@Name', 'VARCHAR(50)') AS DataSourceName
I tried not adding a default namespace but I got no rows with that.
WITH XMLNAMESPACES (
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' as ns1,
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' as ns2,
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
)
SELECT path as ReportPath, name AS ReportName, CreationDate, ModifiedDate
, q.value('@Name', 'VARCHAR(50)') AS DataSourceName
, q.value('ns1:DataSourceReference[1]', 'VARCHAR(255)') AS DataSourceReference
, q.value('rd:SecurityType[1]', 'VARCHAR(50)') AS SecurityType
, x.value('ns1:ConnectString[1]', 'VARCHAR(50)') AS ConnectString
FROM (
SELECT path, name, CreationDate, ModifiedDate
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer.dbo.Catalog WHERE [Type]=2 and Name='ReportCheck') a
CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') d(q)
OUTER APPLY q.nodes('ns1:ConnectionProperties') r(x)
union
SELECT path as ReportPath, name AS ReportName, CreationDate, ModifiedDate
, q.value('@Name', 'VARCHAR(50)') AS DataSourceName
, q.value('ns2:DataSourceReference[1]', 'VARCHAR(255)') AS DataSourceReference
, q.value('rd:SecurityType[1]', 'VARCHAR(50)') AS SecurityType
, x.value('ns2:onnectString[1]', 'VARCHAR(50)') AS ConnectString
FROM (
SELECT path, name, CreationDate, ModifiedDate
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer.dbo.Catalog WHERE [Type]=2 and Name='ReportCheck') a
CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') d(q)
OUTER APPLY q.nodes('ns2:ConnectionProperties') r(x)
-
Stephen Yale (6/9/2015)
I think I am close but not close enough 🙁Quick question, can you post some sample data?
😎
-
The items highlighted pink are not correct
-
Quick suggestion, add a where clause
😎
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' as ns1,
'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' as ns2,
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition'
)
SELECT path as ReportPath, name AS ReportName, CreationDate, ModifiedDate
, q.value('@Name', 'VARCHAR(50)') AS DataSourceName
, q.value('ns1:DataSourceReference[1]', 'VARCHAR(255)') AS DataSourceReference
, q.value('rd:SecurityType[1]', 'VARCHAR(50)') AS SecurityType
, x.value('ns1:ConnectString[1]', 'VARCHAR(50)') AS ConnectString
FROM (
SELECT path, name, CreationDate, ModifiedDate
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer.dbo.Catalog WHERE [Type]=2 and Name='ReportCheck') a
CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') d(q)
OUTER APPLY q.nodes('ns1:ConnectionProperties') r(x)
WHERE q.value('ns1:DataSourceReference[1]', 'VARCHAR(255)') IS NOT NULL
union
SELECT path as ReportPath, name AS ReportName, CreationDate, ModifiedDate
, q.value('@Name', 'VARCHAR(50)') AS DataSourceName
, q.value('ns2:DataSourceReference[1]', 'VARCHAR(255)') AS DataSourceReference
, q.value('rd:SecurityType[1]', 'VARCHAR(50)') AS SecurityType
, x.value('ns2:onnectString[1]', 'VARCHAR(50)') AS ConnectString
FROM (
SELECT path, name, CreationDate, ModifiedDate
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer.dbo.Catalog WHERE [Type]=2 and Name='ReportCheck') a
CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') d(q)
OUTER APPLY q.nodes('ns2:ConnectionProperties') r(x)
WHERE q.value('ns2:DataSourceReference[1]', 'VARCHAR(255)') IS NOT NULL;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply