Wednesday, September 21, 2011

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


OPENXML provides a rowset view over an XML document.


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


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.
[ , 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


SET @BookDetails =

                        <book_title>Dotnet reference</book_title>
            <book_title>Donet complete review</book_title>

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)  

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.

No comments :

Post a Comment