1

Topic: XML parsing

Prompt please...
It is necessary to receive the list of users and roles on each folder of reports Reporting Service. I could find the description of the list of roles only in column XMLDescription of table SecData of basis ReportServer.

<Policies>
<Policy>
<GroupUserName>
MYDOMAIN\DEVPROG1
</GroupUserName>
<GroupUserId>
AQUAAAAAAAUVAAAA/TdCQBroe1YH5TsrpC4AAA ==
</GroupUserId>
<Roles>
<Role>
<Name>
MS Analyst
</Name>
</Role>
</Roles>
</Policy>
<Policy>
<GroupUserName>
MYDOMAIN\DEVPROG2
</GroupUserName>
<GroupUserId>
AQUAAAAAAAUVAAAA/TdCQBroe1YH5Tsr3WAAAA ==
</GroupUserId>
<Roles>
<Role>
<Name>
Browser
</Name>
</Role>
<Role>
<Name>
Content Manager
</Name>
</Role>
<Role>
<Name>
MS Analyst
</Name>
</Role>
</Roles>
</Policy>
</Policies>

Prompt how to read from here user name (tag contents/Policies/Policy/GroupUserName) and all roles concerning it (contents of tags/Policies/Policy/Roles/Role/Name)?

2

Re: XML parsing

iroquai;

select
p.n.value (' GroupUserName [1] ', ' varchar (255) ')
, r.n.value (' Name [1] ', ' varchar (255) ')
from @xml.nodes ('/Policies/Policy ') p (n)
cross apply p.n.nodes (' Roles/Role ') as r (n)

3

Re: XML parsing

Thanks huge!

4

Re: XML parsing

iroquai, or so

select
n.v.value ('./././GroupUserName [1] ', ' nvarchar (50) ')
,n.v.value ('. ', ' nvarchar (50) ')
from @xml.nodes ('//Policy/Roles/Role/Name ') n (v)

5

Re: XML parsing

And how to make too most not from a variable, and with XML a table column?

6

Re: XML parsing

iroquai, through cross/outer apply