SQL Server XML to table

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.