How to join on two xml fields with unknown parameter names at design time

I have two tables that need to be joined based on matching xml. The use of xml is because unknown set of values at design time. I have written up a 3 senarios in below. Thanks for any help you can provide.

TableA

Id

xml

Table B

Id

xml

Table A

1, <parms><parm><name>Parm1</name><value>X</value></parm></parms>

2, <parms><parm><name>Parm1</name><value>Y</value></parm>

<parm><name>Parm2</name><value>123</value></parm></parms>

3, <parms><parm><name>Parm1</name><value>Y</value></parm>

<parm><name>Parm2</name><value>578</value></parm></parms>

Table B

7, <parms><parm><name>Parm1</name><value>X</value></parm></parms>

8, <parms><parm><name>Parm1</name><value>Y</value></parm>

<parm><name>Parm2</name><value>123</value></parm></parms>

9, <parms><parm><name>Parm1</name><value>Y</value></parm>

<parm><name>Parm2</name><value>123</value></parm>

<parm><name>Parm2</name><value>578</value开发者_Python百科></parm></parms>

Query Something like this but I need help with the join.

select A.Id  
     , B.Id  
  from TableA A  
 inner join TableB B  
    on ???

Expected Results

A.Id   B.Id  
1       7  
2       8  
3       8  
3       9  

---

declare @TableA table (id int, xmlCol xml)
declare @TableB table (id int, xmlCol xml)

insert into @TableA values
(1, '<parms><parm><name>Parm1</name><value>X</value></parm></parms>'),
(2, '<parms><parm><name>Parm1</name><value>Y</value></parm>
<parm><name>Parm2</name><value>123</value></parm></parms>'),
(3, '<parms><parm><name>Parm1</name><value>Y</value></parm>
<parm><name>Parm2</name><value>578</value></parm></parms>')

insert into @TableB values
(7, '<parms><parm><name>Parm1</name><value>X</value></parm></parms>'),
(8, '<parms><parm><name>Parm1</name><value>Y</value></parm>
<parm><name>Parm2</name><value>123</value></parm></parms>'),
(9, '<parms><parm><name>Parm1</name><value>Y</value></parm>
<parm><name>Parm2</name><value>123</value></parm>
<parm><name>Parm2</name><value>578</value></parm></parms>')

;with cteA as
(
  select 
    A.id,
    p.value('name[1]', 'varchar(50)') as Name,
    p.value('value[1]', 'varchar(50)') as Value
  from @TableA as A
    cross apply A.xmlCol.nodes('parms/parm') as n(p)
), cteB as
(
  select 
    B.id,
    p.value('name[1]', 'varchar(50)') as Name,
    p.value('value[1]', 'varchar(50)') as Value
  from @TableB as B
    cross apply B.xmlCol.nodes('parms/parm') as n(p)
)
select distinct
  A.id as 'A.id',
  (select top 1 B.id
   from cteB as B
   where B.Name = B.Name and
         B.Value = A.Value
   order by B.id asc) as 'B.id'
from cteA as A
order by 1, 2