xmldiff to sql (xml comparison)

Question

EDIT: Decided tp use the patch utility and generated the SQL from there

using Using the XML Diff and Patch Tool in Your Applications

to compare database structures using XML, I have managed to create a file containing the differences in xml. I would like to take the final product and apply the sql "Alter" statements. Has anyone used this tool and done so? Is there an easier way to create the 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"&gt;
    <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

A well-written XSLT would give you a quick, easy way to do this if you're doing it a lot.

Simple algorithm:

• Create a master template that selects everything inside <config>.
• Copy everything inside <add> tags. Use a template to match the <add> elements.
• For everything else, either change the value of the output or delete it (based on the diffgram element type) using the @match attribute to decide what to modify. Most likely, this is best accomplished with two separate templates, although you could use one.