将xml转sql

<?xml version="1.0" encoding="UTF-8"?>
<display_abc_de version="090410" type-id="4049" type-name="ABCD_XYZ" type-class="CDE" time-field-name="start_time">

    <record multiple="yes">
        <field>
            <field-name>time</field-name>
            <id>0</id>
            <type>dateTime</type>
        </field>

        <field>
            <field-name>id</field-name>
            <id>1</id>
            <type>int</type>
        </field>

        <field>
            <field-name>ip</field-name>
            <id>4</id>
            <type>unsignedInt</type>
            <subtype>ipv4addr</subtype>
        </field>

        <field>
            <field-name>stringtest</field-name>
            <id>8</id>
            <type>string</type>
            <length>32</length>
        </field>
    </record>
</display_abc_de>

-- Database: vsa
DROP DATABASE vsa;
CREATE DATABASE vsa
   WITH OWNER = postgres
ENCODING = UTF8
CONNECTION LIMIT = -1;
--
-- CDR table Define
--
--
-- Table: AAAAA
--
DROP TABLE IF EXISTS AAAAA;
CREATE TABLE AAAAA
(
<display_abc_de version="090410" type-id="4049" type-name="ABCD_XYZ" type-class="CDE" time-field-name="start_time">     

            time
            timestamp without time zone,      

            id
            integer,      

            ip

            integer,

            strtest
            varchar(32), 

CONSTRAINT CDMA_A1_CC_CDR_key PRIMARY KEY (time)
)
WITH (
OIDS=FALSE
);
ALTER TABLE AAAAA OWNER TO postgres;

#!/bin/bash
processTxt()
{
#第一步删除文件中的多余字符
    #删除文件的版本信息
    sed -i '/^<?xml\ version/d' $1

    sed -i 's/<field>//g' $1
    sed -i 's/<\/field>//g' $1

    sed -i 's/<field-name>//g' $1
    sed -i 's/<\/field-name>//g' $1

    #sed -i 's/<id>//g' $1
    #sed -i 's/<\/id>//g' $1

    #将包含 <id>12</id> 的行直接删除
    sed -i '/<id>/d' $1

    sed -i 's/<type>//g' $1
    sed -i 's/<\/type>//g' $1

    sed -i 's/<length>//g' $1
    sed -i 's/<\/length>//g' $1

    sed -i 's/<record\ multiple="yes">//g' $1
    sed -i 's/<\/record>//g' $1

    #将包含 <subtype>ipv4addr</subtype> 的行直接删除
    #sed -i 's/<subtype>//g' $1
    #sed -i 's/<\/subtype>//g' $1
    sed -i '/<subtype>/d' $1

    #删除最后的<\ddict-行
    sed -i '/^<\/ddict-/d' $1


#第二步进行数据库处理
    #将int替换为integer，这样的行是以3个<tab>开始
    sed -i 's/^\t\t\tint\r$/\t\t\tinteger,/' $1

    #将unsignedInt 替换为 int,
    sed -i 's/unsignedInt/int,/' $1

    #将dateTime 替换为 timestamp without time zone,
    sed -i 's/dateTime/timestamp without time zone,/' $1
    
    #将unsignedShort替换为smallint,
    sed -i 's/unsignedShort/smallint,/' $1
    
    #将unsignedShort替换为smallint,
    sed -i 's/unsignedByte/smallint,/' $1
    
    #将unsignedInt 替换为 integer,
    sed -i 's/unsignedInt/integer,/' $1
    
    #将 string\n32替换为varchar(32)
        #1.找到string的行,N读取下一行
        #2.将紧接着下一行的 \r\n\t\t\t删除
        #3.用varchar( 替换string
        #4.在最后加上),
    #sed -i '/string$/{N;s/\n//;s/string/varchar\(/;s/$/\)/;P;D}' $1
    sed -i '/^\t\t\tstring\r$/{N;s/\r\n\t\t\t//;s/string/varchar\(/;s/\r$/\),/;P;D}' $1
    
#第三步插入数据库的建表语句
    #文件开头插入建表语句
    sed -i '1 i\\(' $1

    sed -i '1 i\CREATE TABLE AAAA' $1    
    
    sed -i '1 i\DROP TABLE IF EXISTS AAAA;' $1

    sed -i '1 i\--' $1
    
    sed -i '1 i\-- Table: AAAA' $1

    sed -i '1 i\--' $1

    sed -i '1 i\--' $1

    sed -i '1 i\-- CDR table Define' $1

    sed -i '1 i\--' $1

    sed -i '1 i\CONNECTION LIMIT = -1;' $1

    sed -i '1 i\ENCODING = 'UTF8'' $1

    sed -i '1 i\ WITH OWNER = postgres' $1


    sed -i '1 i\CREATE DATABASE vsa' $1

    sed -i '1 i\DROP DATABASE vsa;' $1

    sed -i '1 i\-- Database: vsa' $1

#文件末尾插入结束语句
    sed -i '$ a\CONSTRAINT AAAA_key PRIMARY KEY (time)' $1
    sed -i '$ a\)' $1
    sed -i '$ a\WITH (' $1
    sed -i '$ a\OIDS=FALSE' $1
    sed -i '$ a\);' $1
    sed -i '$ a\ALTER TABLE AAAA OWNER TO postgres; ' $1
        
}

#改后缀名 将.txt 改为 .sql
fuc_changeSuffix()
{
    newname=`ls $1|sed -n 's/\.txt/\.sql/p'`
    mv -f $1 $newname
}

echo "start process"


if [ -d temp ]
then
    echo "dir exist"
    rm 1.txt 2.txt
    cp -v temp/1.txt .
    cp -v temp/2.txt .
else
    echo "create temp"
    mkdir temp
    cp -v 1.txt 2.txt temp/
fi

#开始处理
for file in $(find . -maxdepth 1 -name "*.txt" -type f)
do
    echo "process $file"
    processTxt $file
    fuc_changeSuffix $file
done

echo "end process"