代码之家 › 专栏 › 技术社区 › Thomas

xmldiff到sql(xml比较)

xml

Thomas · 技术社区 · 15 年前

编辑:使用补丁实用程序并从中生成SQL

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

<?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>

<?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>

新建.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>

1 回复 | 直到 15 年前

ssamuel 14 年前

如果您经常使用编写良好的XSLT,那么它将为您提供一种快速、简单的方法。

简单算法:

创建一个主模板,选择其中的所有内容<配置>。
对于其他所有内容,要么更改输出的值,要么使用@match属性将其删除(基于diffgram元素类型),以决定要修改的内容。最有可能的情况是,最好使用两个单独的模板来完成,尽管您可以使用一个模板。