[Mysql] 연속된 XML Code를 DB에 넣는 방법(PHP)

우선 연속된 XML 코드를 '조직도raw.txt'에 붙여넣었다.(밑은 xml filedml 예시이다.)

<?xml version="1.0" encoding="UTF-8"?>
<EXPORT_PARAMS>
           <RTAB_LIST>
                     <RTAB rowIdx="0">
                                <PERNR value="00000001" />
                                <ENAME value="가랑이" />
                                <ORGEH value="400001" />
                                <ORGTX value="오징어배팀" />
                                <ZORGTX value="" />
                                <ZLEVEL value="41" />
                                <ZLEVTX value="팀장" />
                                <ZAPTITLE value="012" />
                                <ZAPTITTX value="팀장" />
                                <ZEMAIL value="garangE@abc.com" />
                                <ZADDRE value="" />
                                <ZRSTAT value="재직" />
                                <ZAPCLASS value="" />
                                <ZAPCLATX value="" />
                                <GBDAT value="" />
                                <ZRETRSN_DT value="" />
                     </RTAB>
                     <RTAB rowIdx="1">
                                <PERNR value="00000002" />
                                <ENAME value="나랑이" />
                                <ORGEH value="400001" />
                                <ORGTX value="오징어배팀" />
                                <ZORGTX value="" />
                                <ZLEVEL value="41" />
                                <ZLEVTX value="과장" />
                                <ZAPTITLE value="012" />
                                <ZAPTITTX value="직원" />
                                <ZEMAIL value="narangE@abc.com" />
                                <ZADDRE value="" />
                                <ZRSTAT value="재직" />
                                <ZAPCLASS value="" />
                                <ZAPCLATX value="" />
                                <GBDAT value="" />
                                <ZRETRSN_DT value="" />
                     </RTAB>
                     <RTAB rowIdx="2">
                                <PERNR value="00000003" />
                                <ENAME value="다랑이" />
                                <ORGEH value="400001" />
                                <ORGTX value="오징어배팀" />
                                <ZORGTX value="" />
                                <ZLEVEL value="41" />
                                <ZLEVTX value="사원" />
                                <ZAPTITLE value="012" />
                                <ZAPTITTX value="직원" />
                                <ZEMAIL value="darangE@abc.com" />
                                <ZADDRE value="" />
                                <ZRSTAT value="재직" />
                                <ZAPCLASS value="" />
                                <ZAPCLATX value="" />
                                <GBDAT value="" />
                                <ZRETRSN_DT value="" />
                     </RTAB>
                     <RTAB rowIdx="3">
                                <PERNR value="00000004" />
                                <ENAME value="라랑이" />
                                <ORGEH value="400001" />
                                <ORGTX value="오징어배팀" />
                                <ZORGTX value="" />
                                <ZLEVEL value="41" />
                                <ZLEVTX value="과장" />
                                <ZAPTITLE value="012" />
                                <ZAPTITTX value="직원" />
                                <ZEMAIL value="darangE@abc.com" />
                                <ZADDRE value="" />
                                <ZRSTAT value="재직" />
                                <ZAPCLASS value="" />
                                <ZAPCLATX value="" />
                                <GBDAT value="" />
                                <ZRETRSN_DT value="" />
                     </RTAB>
                     <RTAB rowIdx="4">
                                <PERNR value="00000005" />
                                <ENAME value="마랑이" />
                                <ORGEH value="400001" />
                                <ORGTX value="오징어배팀" />
                                <ZORGTX value="" />
                                <ZLEVEL value="41" />
                                <ZLEVTX value="대리" />
                                <ZAPTITLE value="012" />
                                <ZAPTITTX value="직원" />
                                <ZEMAIL value="rarangE@abc.com" />
                                <ZADDRE value="" />
                                <ZRSTAT value="재직" />
                                <ZAPCLASS value="" />
                                <ZAPCLATX value="" />
                                <GBDAT value="" />
                                <ZRETRSN_DT value="" />
                     </RTAB>
           </RTAB_LIST>
</EXPORT_PARAMS>



<?xml version="1.0" encoding="UTF-8"?>
<EXPORT_PARAMS>
           <RTAB_LIST>
                     <RTAB rowIdx="0">
                                <PERNR value="00000041" />
                                <ENAME value="파랑이" />
                                <ORGEH value="412455" />
                                <ORGTX value="새우잡이배팀" />
                                <ZORGTX value="" />
                                <ZLEVEL value="41" />
                                <ZLEVTX value="팀장" />
                                <ZAPTITLE value="012" />
                                <ZAPTITTX value="팀장" />
                                <ZEMAIL value="ParangE@abc.com" />
                                <ZADDRE value="" />
                                <ZRSTAT value="재직" />
                                <ZAPCLASS value="" />
                                <ZAPCLATX value="" />
                                <GBDAT value="" />
                                <ZRETRSN_DT value="" />
                     </RTAB>
           </RTAB_LIST>
</EXPORT_PARAMS>

.
.
.

 

위의 XML File을 DB에 넣어보자.

<?php
include '../conn.php';
# txt파일 전부를 cmd라는 변수에 입력
$cmd = "cat ./조직도raw.txt";
$res = `$cmd`;
$lines = explode("\n",$res);
$start_flag = "<RTAB rowIdx";
$end_flag = "</RTAB>";
$find_start=0;


$key_names=array();
$finder[0]="PERNR";
$finder[1]="ENAME";
$finder[2]="ORGEH";
$finder[3]="ZLEVEL";
$finder[4]="ZLEVTX";
$finder[5]="ZAPTITLE";
$finder[6]="ZAPTITTX";
$finder[7]="ZEMAIL";
$summary = array();



for($i=0; $i < count($lines); $i++)
{
  $line=$lines[$i];
  $start_pos = strpos($line, $start_flag);
  $end_pos = strpos($line, $end_flag);

  if($start_pos !== false)
  {
    $find_start = 1;
  }

  if($end_pos !== false)
  {
    $find_start = 0;
  }

  if($find_start ==1)
  {
    $dd = explode("=",$line);
    $mkey = $dd[0];
    $mvalue=$dd[1];

    for($m=0; $m < count($finder); $m++)
    {
      $fit_pos = strpos($mkey, $finder[$m]);
      if($fit_pos !== false)
      {
        $tvalue=explode('"',$mvalue);
        if($finder[$m]=="PERNR")
          {
            $sanum = $tvalue[1];
            $summary[$sanum][$finder[$m]]=$tvalue[1];
          }
          else
          {
            $summary[$sanum][$finder[$m]]=$tvalue[1];
          }
      }

    }
  }
}
foreach ($summary as $key => $value) {
  if($value['PERNR']=="")   $value['PERNR']="미확인";	#학번
  if($value['ENAME']=="")   $value['ENAME']="미확인";	#이름
  if($value['ORGEH']=="")   $value['ORGEH']="미확인";	#코드
  if($value['ZLEVTX']=="")  $value['ZLEVTX']="미확인";	#
  if($value['ZEMAIL']=="")  $value['ZEMAIL']="미확인";	#이메일



  $sql =  "INSERT INTO iqms.user_info                                         ";
  $sql .= "(                                                                  ";
  $sql .= "   user_id, passwd, old_passwd, name, deptCD, call_grade,          ";
  $sql .= "		email, role, logintry, voc_ui, voc_ui_edit, admin_ui,           ";
  $sql .= "		admin_ui_edit, dashboard_ui, dashboard_ui_edit, last_logindate, ";
  $sql .= "		pw_default, user_ui, user_ui_edit, bldg_ui, bldg_ui_edit,       ";
  $sql .= "		data_ui, data_ui_edit, car_ui, car_ui_edit, disaster_role       ";
  $sql .= ")                                                                  ";
  $sql .= "VALUES  													  			                          ";
  $sql .= "(                                                                  ";
  $sql .= "		?, '1234', '', ?, ?, ?, ?, 'ROLE_USER', '0', 'Y', 'Y' ,'N',     ";
  $sql .= "		'N', 'Y', 'Y', '1000-01-01 00:00:00', 'N', 'Y', 'Y', 'Y',       ";
  $sql .= "		'Y', 'N', 'N','Y', 'Y', 'NORMAL'    								            ";
  $sql .= ")                                                                  ";
  $sql .= "ON DUPLICATE KEY UPDATE name=?, deptCD=?, call_grade=?, email=?;   ";

  #echo "\n\n".$sql."\n\n";

  $sql_obj = $conn->prepare($sql);
  $sql_obj->bind_param("sssssssss",$value['PERNR'],$value['ENAME'],$value['ORGEH'],$value['ZLEVTX'],$value['ZEMAIL'],$value['ENAME'],$value['ORGEH'],$value['ZLEVTX'],$value['ZEMAIL']);
  $sql_obj->execute();
  $result = $sql_obj->get_result();
}

?>