Hi Colleagues,
I am breaking my head against XMLA that will list all roles existing in a database. I know I need to use Discover. There is <RequestType>DISCOVER_XML_METADATA</RequestType>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions>
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>SSAS_DB_ID</DatabaseID>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
</PropertyList>
</Properties>
</Discover>
But it runs too long and returns several megabytes of information. Yes, it also contains the roles, but the way to get to it is not optimal.
Does anyone know how to get only the <Roles></Roles> part of this XML?
Very unfortunately I cannot make sense out of the MS documentation for my requirement.
Would appreciate any insights on this.
I have also posted the same question on stackoverflow.
June 10, 2020 at 12:15 pm
Have you tried the ASSP project? Namely the DiscoverXMLMetaData function: https://asstoredprocedures.github.io/functions/DiscoverXmlMetadata/
Makes all of this easier.
Thanks Paul. The ASSP is a top 1 answer for this problem. However, I needed to do this with bare SQL. Which I managed.
On a side note, I really struggled a lot to understand the MS XMLA documentation. I needed to be able to get just roles for DB, without additional info, and I could not get any hint from the documentation how to do that. At the end, I managed with this one-size-fits-all DISCOVER_XML_METADATA solution - albeit it returned much more information than I actually needed.
declare @SSASDBName nvarchar(255) = 'SSAS_DB_NAME'
-- when executed from SQL, XMLA Discover has to be wrapped in <Batch>
declare @XMLA nvarchar(MAX)
select @XMLA = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions>
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>'+@SSASDBName+'</DatabaseID>
</RestrictionList>
</Restrictions>
<Properties/>
</Discover></Batch>'
-- get the XML response from SSAS
declare @t table (x XML)
insert @t
EXEC(@XMLA) AT LINKED_SSAS_SERVER -- linked SSAS server
declare @XMLAResult xml
select @XMLAResult = x from @t
-- expand XML response
;with XMLNAMESPACES (default 'urn:schemas-microsoft-com:xml-analysis:rowset')
select
SSAS_DB = X_DB.x.query('./ID').value('.','nvarchar(255)'),
RoleID = X_Roles.x.query('./ID').value('.','nvarchar(255)'),
RoleMember = X_Members.x.query('.').value('.','nvarchar(255)')
from @XMLAResult.nodes('//Database') X_DB(x)
cross apply @XMLAResult.nodes('//Roles/Role') X_Roles(x)
cross apply X_Roles.x.nodes('./Members/Member/Name') X_Members(x)
June 11, 2020 at 6:20 am
Thanks Paul. The ASSP is a top 1 answer for this problem. However, I needed to do this with bare SQL. Which I managed.
On a side note, I really struggled a lot to understand the MS XMLA documentation. I needed to be able to get just roles for DB, without additional info, and I could not get any hint from the documentation how to do that. At the end, I managed with this one-size-fits-all DISCOVER_XML_METADATA solution - albeit it returned much more information than I actually needed.
declare @SSASDBName nvarchar(255) = 'SSAS_DB_NAME'
-- when executed from SQL, XMLA Discover has to be wrapped in <Batch>
declare @XMLA nvarchar(MAX)
select @XMLA = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_XML_METADATA</RequestType>
<Restrictions>
<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
<DatabaseID>'+@SSASDBName+'</DatabaseID>
</RestrictionList>
</Restrictions>
<Properties/>
</Discover></Batch>'
-- get the XML response from SSAS
declare @t table (x XML)
insert @t
EXEC(@XMLA) AT LINKED_SSAS_SERVER -- linked SSAS server
declare @XMLAResult xml
select @XMLAResult = x from @t
-- expand XML response
;with XMLNAMESPACES (default 'urn:schemas-microsoft-com:xml-analysis:rowset')
select
SSAS_DB = X_DB.x.query('./ID').value('.','nvarchar(255)'),
RoleID = X_Roles.x.query('./ID').value('.','nvarchar(255)'),
RoleMember = X_Members.x.query('.').value('.','nvarchar(255)')
from @XMLAResult.nodes('//Database') X_DB(x)
cross apply @XMLAResult.nodes('//Roles/Role') X_Roles(x)
cross apply X_Roles.x.nodes('./Members/Member/Name') X_Members(x)
That's quite the mouthful for such a small amount of data! I agree the documentation of XMLA Discover is a bit lacking, that's why someone created ASSP I suppose. Thanks for posting back the resolution 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply