I need to know if an orignal xml has nodes with occurrences, with the second xml that I want to compare. It is posible to make that compare.

<!-------Original xml----->

<datos>      
      <clave1>017</clave1>
      <clave2>017</clave2>
      <clave3>017</clave3>      
      <Datos2>
        <f>
          <color>1</color>
          <color1>999</color1>   
        </f>
        <f>
          <color>0</color>
          <color1>003</color1>          
        </f>
      </Datos2>      
</datos>

 <!-------second xml that i make change, it could have more occurrences in node f  ----->

<datos>      
      <clave1>017</clave1>
      <clave2>017</clave2>
      <clave3>017</clave3>      
      <Datos2>
        <f>
          <color>2</color>
          <color1>566</color1>   
        </f>
        <f>
          <color>0</color>
          <color1>003</color1>          
        </f>
      </Datos2>      
</datos>

CodePudding user response：

You can use something like this to compare f nodes at the same index

SELECT
  v2.pos,
  colorFirst = v1.color,
  color1First = v1.color1,
  colorSecond = v2.color,
  color1Second = v2.color1
FROM @xml2.nodes('/datos/Datos2/f') x2(f)
CROSS JOIN @xml1.nodes('/datos/Datos2') x1D(datos2)
CROSS APPLY (VALUES(
    x2.f.value('let $i:= . return count(../f[. << $i])   1','int'),
    x2.f.value('(color/text())[1]','varchar(10)'),
    x2.f.value('(color1/text())[1]','varchar(10)')
)) v2(pos, color, color1)
OUTER APPLY x1D.datos2.nodes('f[sql:column("v2.pos")]') x1(f)
CROSS APPLY (VALUES(
    x1.f.value('(color/text())[1]','varchar(10)'),
    x1.f.value('(color1/text())[1]','varchar(10)')
)) v1(color, color1)
WHERE (v1.color IS NULL OR v1.color <> v2.color)
   OR (v1.color1 IS NULL OR v1.color1 <> v2.color1);

db<>fiddle

SQL Server does not support fn:position() so we need to hack it by counting previous nodes.

CodePudding user response：

here is a more generic solution that would need some ajustments if you need more sublevels, or maybe a 'full outer join' if you have more datas on the other xml

select a.position,a.path,a.value ,b.value 
,[change] = case when b.value = a.value then '' else '*' end
from(
select 
     [path]     = '/' a.value('local-name(.)','varchar(max)')   ISNULL('/' sub1Name,'')   ISNULL('/' sub2Name,'')  ISNULL('/' sub3Name,'')
    ,[value]    = ISNULL(sub3Value, ISNULL(sub2Value, sub1Value))
    ,[position] = p1*100   isnull(p2*10 /*max 10 sub levels*/,0)   isnull(p3,0)/*max 10 sub levels*/
from @xml1.nodes('/*') a(a)
/* 3 sub levels --duplicate if more xml sub levels needed */
outer apply(select [p1]=ROW_NUMBER()over(order by (select null)),b.query('./*'), b.value('local-name(.)','varchar(max)'),b.value('.','varchar(max)')  from    a.nodes('./*') b(b)) b(p1,sub1,sub1Name,sub1Value)
outer apply(select [p2]=ROW_NUMBER()over(order by (select null)),c.query('./*'), c.value('local-name(.)','varchar(max)'),c.value('.','varchar(max)')  from sub1.nodes('./*') c(c)) c(p2,sub2,sub2Name,sub2Value)
outer apply(select [p3]=ROW_NUMBER()over(order by (select null)),d.query('./*'), d.value('local-name(.)','varchar(max)'),d.value('.','varchar(max)')  from sub2.nodes('./*') d(d)) d(p3,sub3,sub3Name,sub3Value)
)a
left join(
select 
     [path]     = '/' a.value('local-name(.)','varchar(max)')   ISNULL('/' sub1Name,'')   ISNULL('/' sub2Name,'')  ISNULL('/' sub3Name,'')
    ,[value]    = ISNULL(sub3Value, ISNULL(sub2Value, sub1Value))
    ,[position] = p1*100   isnull(p2*10,0)   isnull(p3,0)
from @xml2.nodes('/*')  a(a)
outer apply(select [p1]=ROW_NUMBER()over(order by (select null)),b.query('./*'), b.value('local-name(.)','varchar(max)'),b.value('.','varchar(max)')  from    a.nodes('./*') b(b)) b(p1,sub1,sub1Name,sub1Value)
outer apply(select [p2]=ROW_NUMBER()over(order by (select null)),c.query('./*'), c.value('local-name(.)','varchar(max)'),c.value('.','varchar(max)')  from sub1.nodes('./*') c(c)) c(p2,sub2,sub2Name,sub2Value)
outer apply(select [p3]=ROW_NUMBER()over(order by (select null)),d.query('./*'), d.value('local-name(.)','varchar(max)'),d.value('.','varchar(max)')  from sub2.nodes('./*') d(d)) d(p3,sub3,sub3Name,sub3Value)
)b on b.path = a.path and a.position = b.position
where b.value <> a.value

db<>fiddle