openxml in sql server

OPENXML (Transact-SQL)

OPENXML provides a rowset view over an XML document. Because OPENXML is a rowset provider, OPENXML can be used in Transact-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear.

https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/xml/examples-using-openxml?view=sql-server-2017

 

XML System Stored Procedures

SQL Server provides the following system stored procedures that are used together with OPENXML:

• sp_xml_preparedocument (Transact-SQL)

• sp_xml_removedocument (Transact-SQL)

  To write queries by using OPENXML, you must first create an internal representation of the XML document by calling sp_xml_preparedocument. The stored procedure returns a handle to the internal representation of the XML document. This handle is then passed to OPENXML. OPENXML provides rowset views of the document based on XPaths. Specifically, this is one row pattern and one or more column patterns.

Note

The document handle that is returned by sp_xml_preparedocument is valid for the duration of the session.

The internal representation of an XML document can be removed from memory by calling the sp_xml_removedocument system stored procedure.

 

已经不建议用这个

https://stackoverflow.com/questions/49272564/openxml-returning-null

https://stackoverrun.com/cn/q/1573581

 

 

 

 

作者:Chuck Lu    GitHub