SQL Server XML to table

Objective

In SQL Server sometimes we need to descendant a table in the past, then we can batch update in the stored procedure, get the corresponding data in batches.

But the parameters of the stored procedure are fixed, so here we can work around the parameters of the XML type, and then convert it directly into the table we need in the stored procedure.

Specific implementation

XML type parameters are already supported in SQL sever, and we can use the OpenXML method to parse XML parameters, OpenXML MSDN.

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

The above can see that OPENXML is three parameters:

The first is generally obtained through a stored procedure sp_xml_preparedocument .

The second argument is an XPath

The third parameter identifier, 1, is the attribute that gets the XML, and 2 is the child node that gets the XML.

Below we demo flag for 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&G t;2016-07-06</depdate> </air> <air> <Dep>CGQ</Dep> <arr>ckg</ar r> <AirCode>3U</AirCode> <FlightNo>3U8864</FlightNo> <cabin>y</ca bin> <DepDate>2016-07-15</DepDate> </air></airs>';DECLARE @handle INT; DECLARE @PrepareXmlStatus INT; EXEC @PrepareXmlStatus=sp_xml_preparedocument@handleOUTPUT,@XML; SELECT  * fromOPENXML (@handle,'/airs/air',2)       with(DepNVARCHAR( -), ArrNVARCHAR( -), AircodeNVARCHAR( -), FlightnoNVARCHAR( -), CabinNVARCHAR( -), Depdate DATE); EXECSp_xml_removedocument@handle;

The final result:

Dep Arr aircode Flightno Cabin depdate
-------------------- -------------------- -------------------- -------------------- -------------------- ----------
SYX ZUH 3U 3u8432 X 2016-07-06
Cgq CKG 3U 3u8864 Y 2016-07-15

The demo of getting the properties is as follows:

DECLARE @XMLXML;SET @XML = '<airs> <air dep= "SYX" arr= "ZUH" aircode= "3U" flightno= "3u8432" cabin= "X" depdate= "2016-07-06" printprice= "<air" ></air> dep= "Cgq" arr= "CKG" aircode= "3U" flightno= "3u8864" cabin= "Y" depdate= "2016-07-15" Print Price= "></air></airs> "';DECLARE @handle INT; DECLARE @PrepareXmlStatus INT; EXEC @PrepareXmlStatus=sp_xml_preparedocument@handleOUTPUT,@XML; SELECT  * fromOPENXML (@handle,'/airs/air',1)       with(DepNVARCHAR( -), ArrNVARCHAR( -), AircodeNVARCHAR( -), FlightnoNVARCHAR( -), CabinNVARCHAR( -), Depdate DATE, PrintpriceDECIMAL( -,2)    ); EXECSp_xml_removedocument@handle;

The results are as follows:

Dep Arr aircode flightno Cabin depdate printp Susan
-------------------- -------------------- -------------------- -------------------- -------------------- ---------- - --------------------------------------
SYX ZUH 3U 3u8432 X 2016-07-06 1000.0 0
Cgq CKG 3U 3u8864 Y 2016-07-15 1500.0 0

Summarize

Using OPENXML can convert XML to the required table, and OPENXML parameters only three, you can run a few times to understand the demo.

SQL Server XML to table