Cross Joining two XML columns, or two (or more) XML variables

Hello,
I am trying to filter an XML variable @.RoleList with the approved roles in a
similiarly structured XML variable called @.ApprovedRoles .
What i mean by "filter" is that i would like to retrieve into an XML
variable only those
"roles" in my @.RoleList that are in the @.ApprovedRoles XML variable.
Any suggestions for this?
Of course I could go the "nvarchar" way, and parse both lists and then after
a comparison ouput only return those roles that match the roles in the
@.ApprovedRoles XML variable.
But how about an SQL Server XQUery way of doing it?
A way to cross or inner *joins* two (or more) XML variables, or cross or
inner *join* two (or more) XML columns?
For examples i tried using the below example:
declare @.RoleList xml, @.ApprovedRoles xml
Set @.RoleList = '<roles><role>N12</role><role>N12</role><role>N13</role>
<role>S39</role><role>S14</role></roles>'
Set @.ApprovedRoles = '<roles><role>N12</role><role>S39</role>
</roles>'
In filterning @.RoleList with @.ApprovedRoles, I would expect the output of:
'<role>N12</role><role>N12</role><role>S39</role>'
Do joint to XML columns i tried it with the table below and then inserted
then inserted @.RoleList & @.ApprovedRoles to the table.
Create Table #XmlTable
(
aRoleList xml,
aAuthorisedList xml
)
Insert INTO @.XmlTable( aRoleList, aAuthorisedList)
VALUES (@.RoleList,@.ApprovedRoles)
The question is, how would I join the columns aRoleList aAuthorisedList, to
ouput all nodes() from aRoleList that are authorized?
I tried also using the two XML variables (@.RoleList,@.ApprovedRoles, not
needing to put them into a table) above, but can't find the syntax to make a
n
XQuery joining XMLs from to separate contexts.
Any suggestions to this too?
declare @.temp nvarchar(200)
set @.temp = cast(@.ApprovedRolesas nvarchar(200) )
Select x1.query('
for $Step1 in /roles,
$Step2 in sql:variable("@.temp")
return $Step2
') as RoleListsHere
from
@.aaRoleList.nodes('//roles') T(x1)
cross apply @.ApprovedRoles.nodes('//roles') TT(xx1)
where TT.xx1.exist('/roles/role') = 1
Regards,
PaulHow about:
SELECT rl.node.value('.','varchar(20)')
FROM @.RoleList.nodes('/roles/role') rl(node)
INNER JOIN @.ApprovedRoles.nodes('/roles/role') ar(node)
ON rl.node.value('.','varchar(20)') =
ar.node.value('.','varchar(20)')
Marc|||or as xml (you might choose to add a DISTINCT too):
SELECT rl.node.value('.','varchar(20)') as 'role'
FROM @.RoleList.nodes('/roles/role') rl(node)
INNER JOIN @.ApprovedRoles.nodes('/roles/role') ar(node)
ON rl.node.value('.','varchar(20)') =
ar.node.value('.','varchar(20)')
FOR XML PATH(''), ROOT('roles')
Marc