OPENXML provides a rowset view over an XML document.
OPENXML can be used in TransactSQL statements in which rowset providers such as a table, view
Syntax:
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
doc
Is the document handle of the internal representation of an XML document. The
internal representation of an XML document is created by
calling sp_xml_preparedocument.
Rowpattern
Is the XPath pattern used to identify the nodes (in the XML document whose handle is
passed in the idocparameter) to be processed as rows.
Flags
Indicates the mapping that should be used between the XML data and the relational
rowset, and how the spill-over column should be filled. flags is an optional input
parameter, and can be one of the following values.
Byte value
Description
Defaults to attribute-centric mapping.
Use the attribute-centric mapping. Can be combined with XML_ELEMENTS.
In this case,attribute-centric mapping is applied first, and thenelementcentric mapping is applied for all columns that are not yet dealt with.
Use the element-centric mapping. Can be combined with XML_ATTRIBUTES.
In this case,attribute-centric mapping is applied first, and thenelementcentric mapping is applied for all columns not yet dealt with.
Can be combined (logical OR) with XML_ATTRIBUTES or XML_ELEMENTS.
In the context of retrieval, this flag indicates that the consumed data should not be
copied to the overflow property@mp:xmltext.
OPENXML function must be used with two system stored
procedures: sp_xml_preparedocument andsp_xml_removedocument. As the names of these
procedures suggest, the former prepares an internal representation of the XML document in memory, and the
latter removes such representation to free up resources. In fact, you can get away without
using sp_xml_removedocument (whether this is a feature or bug, I'm not sure) because SQL Server will
automatically destroy the internal structure once the session that created it disconnects.
Example :
USE PUBS
DECLARE @xml_text VARCHAR(4000), @i INT
SELECT @xml_text = '
<root>
<authors
au_id="172-32-1176"
au_lname="White"
au_fname="Johnson"
phone="408 496-7223"
address="10932 Bigge Rd."
city="Menlo Park"
state="CA"
zip="94025"
contract="1"/>
<authors
au_id="213-46-8915"
au_lname="Green"
au_fname="Marjorie"
phone="415 986-7020"
address="309 63rd St. #411"
city="Oakland"
state="CA"
zip="94618"
contract="1"/>
<authors
au_id="238-95-7766"
au_lname="Carson"
au_fname="Cheryl"
phone="415 548-7723"
address="589 Darwin Ln."
city="Berkeley"
state="CA"
zip="94705"
contract="1"/></root>'
EXEC sp_xml_preparedocument @i OUTPUT, @xml_text
Results:
au_id
172-32-
au_lnam
au_fnam
White
Johnson
1176
213-46-
Green
Marjorie
8915
238-957766
Carson
Cheryl
phone
Address
city
stat
zip
408 496-
10932 Bigge
Menlo
7223
Rd.
Park
415 986-
309 63rd St.
Oakland
7020
#411
415 548-
589 Darwin Ln.
7723
CA
contrac
t
9402
CA
9461
Berkeley
CA
9470
5