php - 选择要从 XML 导入到 mySQL 表的数据

Question

我需要将一个非常大的 XML 文件导入到我的 MySQL 表中。问题是我需要选择要输入的数据。例如 XML 是这样的:

<FLIGHT>
<Airline>SAS</Airline>
<FlightNumber>1</FlightNumber>
<CommercialFlightNumber>SK1</CommercialFlightNumber>
<From>LLA</From>
<To>ARN</To>
<PreparedAt>2015/01/30 00:00</PreparedAt>
<Aircraft>LN-RGI</Aircraft>
<AircraftType>0</AircraftType>
<ClimbMode>1</ClimbMode>
<CruiseMode>100600</CruiseMode>
<DescentMode>1</DescentMode>
<InitAlt>MAX</InitAlt>
<StepClimb>-1</StepClimb>
<MTOW>-1</MTOW>
<MLW>-1</MLW>
<STD>2015/1/19 06:00</STD>
<STA>2015/1/19 07:30</STA>
<MaxPax>0</MaxPax>
<MaxCargo>0</MaxCargo>
<RandomPayload>0</RandomPayload>
<Type>0</Type>
<Repetative>1</Repetative>
<IsMaster>1</IsMaster>
<BeginDate>2015/02/04 00:00</BeginDate>
<EndDate>2015/04/30 23:59</EndDate>
<Days>1</Days>
<Baggage>-1</Baggage>
<Payload>-1</Payload>
<Notes>7388</Notes>
</FLIGHT>

但我只需要“CommercialFlightNumber”、“From”、“To”、“Aircraft”和“Days”。我看了一下this回答并尝试将我的信息放入 php 文件中，但文件变得很大并且程序崩溃了..有什么办法可以做到这一点吗？谢谢!

Answer 1

查看 SQL Server 的nodes():

The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row. [MSDN]

仅将特定 XML 元素插入表中的示例:

declare @table TABLE(CommFlightNo varchar(10), 
                     [From] varchar(3), [To] varchar(3), 
                     Aircraft varchar(10), Days int)
declare @xml xml = '...xml posted in question....'


INSERT INTO @table
SELECT F.x.value('CommercialFlightNumber[1]', 'varchar(10)') as 'ComFlightNo'
      ,F.x.value('From[1]', 'varchar(3)') as 'From'
      ,F.x.value('To[1]', 'varchar(3)') as 'To'
      ,F.x.value('Aircraft[1]', 'varchar(10)') as 'Aircraft'
      ,F.x.value('Days[1]', 'int') as 'Days'
FROM @xml.nodes('//FLIGHT') as F(x)

<强> SQL Fiddle