SQL Server XML轉Table

標籤：

前言　　

在SQL Server中有時候我們需要傳人一個Table過去，然後可以在預存程序中批次更新，批量的擷取相應資料。

但預存程序的參數是固定，所以這裡我們可以變通的傳人xml類型的參數，然後在預存程序中直接將其轉換成我們需要的Table

具體實現

SQL Sever中已經支援XML類型的參數了，這裡我們可以用OPENXML 方法來解析xml參數，OPENXML的MSDN。

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )

以上可以看到OPENXML是三個參數：

第一個一般是通過預存程序sp_xml_preparedocument擷取的。

第二個參數就是xpath

第三個參數標識符，1表示擷取的是xml的屬性，2表示擷取xml的子節點。

下面我們demo一下flag為2的：

DECLARE @XML NVARCHAR(MAX);SET @XML = ‘<airs>    <air>        <Dep>SYX</Dep>        <Arr>ZUH</Arr>        <AirCode>3U</AirCode>        <FlightNo>3U8432</FlightNo>        <Cabin>X</Cabin>        <DepDate>2016-07-06</DepDate>    </air>    <air>        <Dep>CGQ</Dep>        <Arr>CKG</Arr>        <AirCode>3U</AirCode>        <FlightNo>3U8864</FlightNo>        <Cabin>Y</Cabin>        <DepDate>2016-07-15</DepDate>    </air></airs>‘;DECLARE @handle INT;  DECLARE @PrepareXmlStatus INT;  EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML;  SELECT  *FROM    OPENXML(@handle, ‘/airs/air‘, 2)      WITH (    Dep NVARCHAR(20),    Arr NVARCHAR(20),    AirCode NVARCHAR(20),    FlightNo NVARCHAR(20),    Cabin NVARCHAR(20),    DepDate DATE    );  EXEC sp_xml_removedocument @handle; 

其最終的結果：

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