Rowset Function – OPENXML

OPENXML function is used to provide a relational rowset view of data for given XML document. XML document represents data in the form of Elements\Attributes. OPENXML function converts Elements\Attributes form data to relation table [Rows\Columns] format.

Syntax

          OPENXML (@idoc, <row pattern>, <flag>)

          WITH (<Schema Declaration | Table name>)

@idoc                    -- It is internal representation of XML document which can be created using          sp_XML_preparedocument stored procedure.

Row Pattern         -- It indicates XPath pattern which is used to identify the nodes to be processed as rows in the result set.

Flag             - It indicates the mapping that should be used between XML and relational rowset. And tells how the columns in the relational rowset should be filled.

1)   0                   - Attribute-centric mapping which is default.

2)   1                   - It uses the Attribute-Centric mapping and can be combined with XML_ELEMENTS

3)   2                   - It uses the Element-Centric mapping and can be combined with XML_ATTRIBUTES.

4)   8                   - It can be combined with XML-ELEMENTS OR XML-ATTRIBUTES (LOGICAL-OR Operation).

If you already have the defined Table Name with desired patterns, then you specify the Table name in the WITH Clause. Otherwise specify the Schema declaration in the form of [<Column Name> <Column Type> <Column Pattern>]. Column Pattern is an XPath pattern which defines how XML nodes should be mapped with the columns.

Pre-Configuration Setup

In order to use OPENXML Rowset function, the following pre-configuration has to be set.

1.   Make sure that Ad Hoc Distributed Queries advanced configuration option has been enabled on your SQL Server instance. If not yet enabled, then use the below script to enable it.

SP_CONFIGURE 'show advanced options', 1

RECONFIGURE;

EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1

RECONFIGURE;

Example

Below example provides relational rowset format of data from XML data format.

DECLARE @idoc int

DECLARE @doc varchar(1000)

SET @doc ='

<ROOT>

<Student StudentID="101" StudentName="James">

   <Department DeptID="1" StudentID="101" JoiningDate="2001-09-05T00:00:00">

      <ScoreReport Subject="Physics" Mark="90"/>

      <ScoreReport Subject="Chemistry" Mark="85"/>

            <ScoreReport Subject="Biology" Mark="92"/>

            <ScoreReport Subject="Hindi" Mark="75"/>

            <ScoreReport Subject="English" Mark="89"/>

   </Department>

</Student>

</ROOT>'

--Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT stmt using OPENXML rowset provider

SELECT *

FROM   OPENXML (@idoc, '/ROOT/Student/Department/ScoreReport',2)

         WITH (

             StudentID  INT                        '../@StudentID',

           DeptID        INT                        '../@DeptID',

          JoiningDate   DATETIME              '../@JoiningDate',

          Subject         VARCHAR(10)         '@Subject',

          Mark               INT                     '@Mark'

                                        )

Also refer the below snapshot for the results.