xmldiff与sql (xml比较)

Question

编辑:决定使用补丁实用程序并从那里生成SQL

使用Using the XML Diff and Patch Tool in Your Applications

为了使用XML比较数据库结构，我设法创建了一个包含xml差异的文件。我想使用最终产品并应用sql "Alter“语句。有没有人用过这个工具？有没有更简单的方法来创建sql？

diff.xml

<?xml version="1.0" encoding="utf-8"?>
<xd:xmldiff version="1.0" srcDocHash="12606910410446600483" options="IgnoreChildOrder IgnoreNamespaces IgnorePrefixes " fragments="no" xmlns:xd="http://schemas.microsoft.com/xmltools/2002/xmldiff">
    <xd:node match="2">
        <xd:add>
            <Information_Schema.Columns CHARACTER_MAXIMUM_LENGTH="10" CHARACTER_OCTET_LENGTH="20" CHARACTER_SET_NAME="UNICODE" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" COLUMN_NAME="test_column" DATA_TYPE="nchar" IS_NULLABLE="YES" ORDINAL_POSITION="14" TABLE_CATALOG="TMLIGHT_Master" TABLE_NAME="config" TABLE_SCHEMA="dbo" />
        </xd:add>
        <xd:add>
            <Information_Schema.Columns COLUMN_DEFAULT="((0))" COLUMN_NAME="position" DATA_TYPE="int" IS_NULLABLE="YES" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" ORDINAL_POSITION="13" TABLE_CATALOG="TMLIGHT_Master" TABLE_NAME="config" TABLE_SCHEMA="dbo" />
        </xd:add>
        <xd:node match="12">
            <xd:change match="@COLUMN_NAME">record_status_id</xd:change>
            <xd:add type="2" name="COLUMN_DEFAULT">((1))</xd:add>
        </xd:node>
        <xd:node match="11">
            <xd:change match="@COLUMN_NAME">controller_type_id</xd:change>
        </xd:node>
        <xd:remove match="13-14" />
    </xd:node>
</xd:xmldiff>

original.xml

<?xml version="1.0" encoding="utf-8"?>
<config>
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="id" ORDINAL_POSITION="1" IS_NULLABLE="NO" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="version_id" ORDINAL_POSITION="2" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="name" ORDINAL_POSITION="3" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="svpip" ORDINAL_POSITION="4" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="username" ORDINAL_POSITION="5" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="password" ORDINAL_POSITION="6" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="serial_number" ORDINAL_POSITION="7" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="short_range" ORDINAL_POSITION="8" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="longrange" ORDINAL_POSITION="9" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="history_period" ORDINAL_POSITION="10" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="controllertype" ORDINAL_POSITION="11" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="controller_type_id" ORDINAL_POSITION="12" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="record_status_id" ORDINAL_POSITION="13" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="position" ORDINAL_POSITION="14" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
</config>

new.xml

<config>
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="id" ORDINAL_POSITION="1" IS_NULLABLE="NO" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="version_id" ORDINAL_POSITION="2" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="name" ORDINAL_POSITION="3" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="svpip" ORDINAL_POSITION="4" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="username" ORDINAL_POSITION="5" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="password" ORDINAL_POSITION="6" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="serial_number" ORDINAL_POSITION="7" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="short_range" ORDINAL_POSITION="8" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="longrange" ORDINAL_POSITION="9" IS_NULLABLE="YES" DATA_TYPE="varchar" CHARACTER_MAXIMUM_LENGTH="50" CHARACTER_OCTET_LENGTH="50" CHARACTER_SET_NAME="iso_1" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="history_period" ORDINAL_POSITION="10" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="controller_type_id" ORDINAL_POSITION="11" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="record_status_id" ORDINAL_POSITION="12" COLUMN_DEFAULT="((1))" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="position" ORDINAL_POSITION="13" COLUMN_DEFAULT="((0))" IS_NULLABLE="YES" DATA_TYPE="int" NUMERIC_PRECISION="10" NUMERIC_PRECISION_RADIX="10" NUMERIC_SCALE="0" />
    <Information_Schema.Columns TABLE_CATALOG="TMLIGHT_Master" TABLE_SCHEMA="dbo" TABLE_NAME="config" COLUMN_NAME="test_column" ORDINAL_POSITION="14" IS_NULLABLE="YES" DATA_TYPE="nchar" CHARACTER_MAXIMUM_LENGTH="10" CHARACTER_OCTET_LENGTH="20" CHARACTER_SET_NAME="UNICODE" COLLATION_NAME="SQL_Latin1_General_CP1_CI_AS" />
</config>

Answer 1

如果您经常使用XSLT，那么编写良好的XSLT将为您提供一种快速、简单的方法来完成此任务。

简单的算法：

• 创建一个选择其中所有内容的主模板。
• 复制标记中的所有内容。使用模板来匹配元素。
• 对于其他所有内容，要么更改输出的值，要么使用@match属性删除它(基于diffgram元素类型)，以确定要修改的内容。最有可能的情况是，使用两个独立的模板来实现这一点是最好的，尽管您也可以使用一个。