OPENXML allows access to XML
data as though it is a relational rowset. It provides rowset view of internal representation
of XML document. The records in the rowset can be stored in database table. Above
illustration explain the process of OPENXML
OPENXML syntax:
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags
byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Arguments:
idoc:
idoc is the document handle of the
internal representation of an XML document.
rowpattern:
rowpattern is the xPath
representation to identify the nodes
flags:
flags indicates the mapping
that should be used between the XML data and the relational rowset. Following table represents flags byte value and description
Byte Value
|
Description
|
0
|
Defaults to attribute-centric mapping
|
1
|
Use the attribute-centric mapping it can be
combined with XML_ELEMENTS
|
2
|
Use the element centric mapping it can be combined with XML_ATTRIBUTES
|
8
|
Can be combined with XML_ELEMENTS or XML_ATTRIBUTES
|
To write queries against an XML
document by using OPENXML, you must first call sp_xml_preparedocument. This
parses the XML document and returns a handle to the parsed document that is
ready for consumption. The
parsed document is a document object model (DOM) tree representation of various
nodes in the XML document. The document handle is passed to OPENXML. OPENXML
then provides a rowset view of the document, based on the parameters passed to
it.
The
internal representation of an XML document must be removed from memory by
calling the sp_xml_removedocument system
stored procedure to free the memory.
Query:
declare @XMLDocHandle INT
declare @xml_data varchar(MAX)='<PersonalDetails>
<Person>
<name>Sachin</name>
<age>24</age>
<sex>Male</sex>
<city>Chennai</city>
</Person>
<Person>
<name>Mathews</name>
<age>30</age>
<sex>Male</sex>
<city>New Delhi</city>
</Person>
</PersonalDetails>'
EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @xml_data
INSERT INTO
PersonalDetails
(Name,
Age,
Sex,
City
)
SELECT NULLIF(name,'')
,NULLIF(age,'')
,NULLIF(sex,'')
,NULLIF(city,'')
FROM OPENXML (@XMLDocHandle, N'/PersonalDetails/Person',2)
WITH
(name
varchar(50),
age varchar(50),
sex varchar(50),
city varchar(50)
)
EXEC sp_xml_removedocument @XMLDocHandle
select * from PersonalDetails
Note:
For more information please follow the
link given below.
http://technet.microsoft.com/en-us/library/ms175160.aspx