OPENXML

OPENXML( idoc INT, rowpattern VARCHAR, flags INT )

• idoc: Represents the ID of the document to be opened. This parameter specifies the document handle for the internal representation of the XML document. Int data type is used.
• rowpattern: This defines the XPath pattern to identify the row’s element or attribute. It is a VARCHAR data type. It helps in parsing the XML document to provide row-structured data.
• flags: Specifies how the XML data should be processed. This is an INT data type. Different flags dictate whether meta-information is provided, whether XML namespaces are removed or not, and whether binary base64-encoded data is decoded or not.

Example

DECLARE @xml XML
SET @xml =
'<Books>
    <Book>
        <Title>Book1</Title>
        <Author>Author1</Author>
    </Book>
    <Book>
        <Title>Book2</Title>
        <Author>Author2</Author>
    </Book>
</Books>'

SELECT *
FROM OPENXML (@idoc, '/Books/Book',2)
WITH (Title  varchar(50)  'Title',
      Author varchar(50)  'Author');

Output

+-------+--------+
| Title | Author |
+-------+--------+
| Book1 | Author1|
| Book2 | Author2|
+-------+--------+

Explanation

The example code first declares and sets an example XML variable containing information about books. It then uses OPENXML to provide rowset view over the XML document. The SELECT statement extracts the ‘Title’ and ‘Author’ for each ‘Book’ and shows them as a table. The output demonstrates the tabular representation of the extracted data.