Note that the document handle returned by sp_xml_preparedocument is valid for the duration of the batch and not the session. If and in the XML document are subelements, the element-centric mapping retrieves the values. The can also be of xml type or of (n)varchar(max) type. Create an internal representation of the XML document.ĮXEC sp_xml_preparedocument OUTPUT, Execute a SELECT statement using OPENXML rowset provider.ĮXEC sp_xml_removedocument is the result: CustomerID ContactNameīecause the elements do not have any subelements, if the same SELECT statement is executed with flags set to 2 to indicate element-centric mapping, the values of CustomerID and ContactName for both the customers are returned as NULL. The SELECT statement then retrieves all the columns in the rowset provided by OPENXML. Therefore, the ColPattern parameter is not specified in SchemaDeclaration.

In SchemaDeclaration, in the WITH clause, the specified ColName values match the corresponding XML attribute names. As a result, the XML attributes map to the columns in the rowset defined in SchemaDeclaration. The flags parameter value is set to 1 and indicates attribute-centric mapping. Rowpattern (/ROOT/Customer) identifies the nodes to process. The OPENXML statement illustrates the following: This document handle is passed to OPENXML. The OPENXML statement retrieves customer information in a two-column rowset, CustomerID and ContactName, from the XML document.įirst, the sp_xml_preparedocument stored procedure is called to obtain a document handle.

The XML document in this example is made up of the, , and elements. Executing a simple SELECT statement with OPENXML The specified ColPattern determines the mapping, attribute-centric or element-centric, and also the behavior in dealing with overflow and unconsumed data. The flags value is ignored if ColPattern is specified in SchemaDeclaration. If no ColPattern is specified in the SchemaDeclaration, the mapping specified in flags is assumed. The flags value provides default mapping. For example, if the pattern ends in an element or an attribute, a row is created for each element or attribute node that is selected by rowpattern. Additionally, rowpattern is expressed in the XPath pattern language that is used in the MSXML XPath implementation. In retrieving the data, rowpattern is used to identify the nodes in the XML document that determine the rows. For more information about how to specify metaproperties in OPENXML, see Specify Metaproperties in OPENXML. The examples show all aspects of OPENXML, but do not specify metaproperties in OPENXML. For information about the syntax of OPENXML, see OPENXML (Transact-SQL). The examples in this topic show how OPENXML is used to create a rowset view of an XML document. Applies to: SQL Server (all supported versions) Azure SQL Database

Excel import xml leaves blank table row