none
compare xml file

    Question

  • hi

    i have 2 xml file below like:

    <root>
      <Name>toni</Name>
      <age>20</age>
      <Tel>
        <num1>7777</num1>
        <num2>8888</num2>
      </Tel>
    </root>
    
    -------------xml2---------
    <root>
      <Name>toni</Name>
      <age>20</age>
      <Tel>
        <num1>7777</num1>
        <num2>1111</num2>
      </Tel>
    </root>
    

    now i want find diff nodes between them

    for example for upper

    diff is node num2

    please help me for create query

    thanks

    Saturday, July 13, 2013 2:32 PM

All replies

  • Refer the below sample T-SQL statements to read XML file and query XML for comparision,

    declare @handle int, @XML xml
    ;WITH XmlFile1 (Contents) AS (
    	SELECT CONVERT (XML, BulkColumn) 
    	FROM OPENROWSET (BULK 'C:\testing_sample\xml1.xml', SINGLE_BLOB) AS XmlData
    ), XmlFile2 (Contents) AS (
    	SELECT CONVERT (XML, BulkColumn) 
    	FROM OPENROWSET (BULK 'C:\testing_sample\xml2.xml', SINGLE_BLOB) AS XmlData
    )
    SELECT @XML = Contents FROM XmlFile1
    exec sp_xml_preparedocument @handle out, @XML
    select 'xml1' as colid, * from openxml(@handle, '/root/.',2)  with ([Name] nvarchar(100) ,age int)
    select 'xml1' as colid, * from openxml(@handle, '/root/Tel', 2) with ([Name] nvarchar(100),age int , num1 int, num2 int)
    exec sp_xml_removedocument @handle


    Regards, RSingh

    Sunday, July 14, 2013 4:04 AM
  • thanks for your answer

    in up code you write /root/Tel

    but i'll diff between nodes , i don't know where id diff

    it's dynamic

    please help me

    thanks

    Sunday, July 14, 2013 3:48 PM