其最终的结果：

Dep                  Arr                  AirCode              FlightNo             Cabin                DepDate
-------------------- -------------------- -------------------- -------------------- -------------------- ----------
SYX                  ZUH                  3U                   3U8432               X                    2016-07-06
CGQ                  CKG                  3U                   3U8864               Y                    2016-07-15

获取属性的demo如下：

DECLARE @XML XML;
SET @XML = ‘<airs>
    <air Dep="SYX" Arr="ZUH" AirCode="3U" FlightNo="3U8432" Cabin="X" DepDate="2016-07-06" PrintPrice="1000"></air>
    <air Dep="CGQ" Arr="CKG" AirCode="3U" FlightNo="3U8864" Cabin="Y" DepDate="2016-07-15" PrintPrice="1500"></air>
</airs>‘;

DECLARE @handle INT;  
DECLARE @PrepareXmlStatus INT;  

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML;  

SELECT  *
FROM    OPENXML(@handle, ‘/airs/air‘, 1)  
    WITH (
    Dep NVARCHAR(20),
    Arr NVARCHAR(20),
    AirCode NVARCHAR(20),
    FlightNo NVARCHAR(20),
    Cabin NVARCHAR(20),
    DepDate DATE,
    PrintPrice DECIMAL(18,2)
    );  


EXEC sp_xml_removedocument @handle; 

结果如下：

Dep                  Arr                  AirCode              FlightNo             Cabin                DepDate    PrintPrice
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- ---------------------------------------
SYX                  ZUH                  3U                   3U8432               X                    2016-07-06 1000.00
CGQ                  CKG                  3U                   3U8864               Y                    2016-07-15 1500.00

总结

用OPENXML可以将xml转换为需要的TABLE，而且OPENXML的参数只有三个，大家多跑几次demo就可以了解了。

SQL Server XML转Table

标签：