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 

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