在PHP中将XML数据提取到MySQL表中

I have an xml file in the format

<DevInfo>

  <SoftwareVer>1.2.3.4.5.6</SoftwareVer>

  <HardWareVer>V001</HardWareVer>

  <BiosVer>T15121501</BiosVer>

  ...

</DevInfo>

I need to extract the hardware version out of the xml file and place it in another table's field device_status.hardware_version using php and mysql. I also need to extract the bios version and software version and place them in fields in the device_status table.

I have other xml files that have elements that I need to extract into other fields in the device_status table. For example:

<GSensor>

   <id>2</id>

   <value>200</value>

   ...

 </GSensor>

This parameter is slightly different from the previous example as the value's meaning depends on the id. For example, if the id is 0, then the value specifies the x-sensor value and an id of 1 specifies a y-sensor value. I will update either x_sensor or y_sensor in the device_status table depending on the id.

How would I go about identifying which value goes where in the device_status table?

I was thinking of having a bridging table that specifies the path to the values in the XML file and the path to the ids should it have an id and then marry that to the field name in the device_status table. The fields would be something like:

  • value_path
  • identifier_path
  • identifier_value
  • status_fieldname

UPDATED FIELD NAMES

  • header
  • identifier_key
  • identifier_value
  • value_key
  • device_status_fieldname

Another option is to name the fields in the device_status table as DevInfo__SoftwareVer where I can marry the path in one step. I am against this option, because it makes for untidy field names and it also gets more untidy when the XML id is involved.

I need to account for both formats in one PHP file.

UPDATE

PHP thought process:

  • extract heading from XML and query bridging table (xmlparam_devicestatus) for entries with that header
  • use identifier_key to find the identifier value in the XML file
  • find the record with that identifier value from resultset and retrieve the value_key to extract the required value from the XML file
  • retrieve the status_fieldname of the record and update that field in device_status with the value extracted from the XML file

I have tried to condense this so if something doesn't make sense, please request an explanation. I think that a bridging table is the best way, but any suggestions are still welcome.