Converting xml document data into a table rowset view in SQL server

OPENXML:-

OPENXML provides a rowset view over an XML document.

Syntax 

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) 
[WITH (SchemaDeclaration | TableName)]

sp_xml_preparedocument:-

Reads the XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. sp_xml_preparedocument limits the number of elements that can be open at one time to 256.

Syntax 

sp_xml_preparedocument

hdoc 

OUTPUT

[ , xmltext ]

[ , xpath_namespaces ] 

This example creates an internal representation of the XML image using sp_xml_preparedocument. A SELECT statement using an OPENXML rowset provider is then executed against the internal representation of the XML document.

DECLARE @BookDetails xml

DECLARE @handle INT

SET @BookDetails =

'<Books>

                  <Book>

                        <author_id>1</author_id>

                        <author_fname>Stephen</author_fname>

                        <author_lname>Smith</author_lname>

                        <book_title>Dotnet reference</book_title>

                        <price>30.50$</price>

                  </Book>

                  <Book>

                        <author_id>2</author_id>

                        <author_fname>John</author_fname>

                        <author_lname>Arthor</author_lname>

            <book_title>Donet complete review</book_title>

                        <price>35.50$</price>

                  </Book>

             </Books>'

EXEC sp_xml_preparedocument @handle OUTPUT, @BookDetails   

SELECT * FROM OPENXML (@handle, '/Books/Book', 2) WITH

  (author_id VARCHAR(5),

   author_fname NVARCHAR(500),

   author_lname NVARCHAR(500),

   book_title NVARCHAR(100),

   price nvarchar(10)  

  )

Output:-
author_id  author_fname   author_lname  book_title              Price  

1          Stephen        Smith         Dotnet reference        30.50$

2          John           Arthor        Donet complete review   35.50$

The OPENXML rowset provider creates a five-column rowset (author_id, author_fname, author_lname, book_title and price) from which the SELECT statement retrieves the all the necessary columns.

The flag value is set to 2 indicating attribute-centric mapping. Therefore, the XML attributes map to the columns in the rowset. The rowpattern specified as /Books/Book identifies the <Books> nodes to be processed.

If the same SELECT statement is executed with flags set to any value other than 2, indicating element-centric mapping, the values of author_id, author_fname, author_lname, book_title and price for both of the book records in the XML document are returned as NULL.