SQL Server XML转Table

hq8501

前言　　
　　在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就可以了解了。